0

I want to write a macro in VBA that will find the last non empty cell in a column but there can be empty cells present in between. I want my program to search further if there is any non empty cell present in a column after it finds a blank cell. Then I want to store the value in some variable a.

The code that I used is a = Range("B1").End(xlDown).value, but this stops when it finds an empty cell. Can anyone tell me how to solve this?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
zih
  • 3
  • 3
  • Possible duplicate of [Better way to find last used row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) – Foxfire And Burns And Burns Jul 30 '19 at 09:22
  • Thanks. This code gives the cell that is non empty. That means if in a coulmn the 5th cell from the above is non empty the code gives 5. But I want the value that the cell contains. I tried with `Range("B" & Rows.Count).End(xlUp).value.Row` but it didn't work. Can you please tell me the problem. – zih Jul 30 '19 at 09:35

2 Answers2

1

Just in case you need if for the future:

Range("B" & Rows.Count).End(xlUp).value.Row will return the row number of the last non empty cell in column B.

Range("B" & Range("B" & Rows.Count).End(xlUp).value.Row).Value will return the value of the last non empty cell in column B. So will do Cells(Range("B" & Rows.Count).End(xlUp).value.Row,2).Value

Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38
  • 1
    Very correct! also note, that in this case `a = [INDEX(B:B,MAX((B:B<>"")*(ROW(B:B))))]` will work :) – JvdV Jul 30 '19 at 10:25
  • Just a quick question. Does it work with cells as well? Instead of writing range can I write the code using cells? Something like `cells(5, 2 & cells.count).End(xlUp).Value`. – zih Jul 30 '19 at 12:18
  • @zih `Cells` first number is row number, and second one is column number, so if you use `cells(5, 2 & cells.count)` it would throw an error. You can use it as `Cells(Rows.count,5).End(xlUp).Value` to get the value of last non empty cell in column 5 (that means column E) – Foxfire And Burns And Burns Jul 30 '19 at 12:23
0

I think you want to have it this way?

Dim a As Integer
Dim b As String
a = ThisWorkbook.ActiveSheet.Range("B1000000").End(xlUp).Row
b = ThisWorkbook.ActiveSheet.Range("B" & a).Value

that way it'll look for the first used row from B 1.000.000 going up. a will be the number of the last used row.

KawaRu
  • 64
  • 1
  • 9
  • Thanks for the suggestion. But it does exactly the same as `Range("B" & Rows.Count).End(xlUp).Row`. That means it gives the index of the cell but not the value that it contains. I want to know the value. – zih Jul 30 '19 at 09:43
  • I have edited my answer. You can use the row number to get the value out of it. – KawaRu Jul 30 '19 at 09:44
  • Thanks so much. It worked. Also `a = ThisWorkbook.ActiveSheet.Range("B1000000").End(xlUp).value` works perfectly. – zih Jul 30 '19 at 09:46