Finding the value containing in the last non empty cell even if there is blank cell in the middle in excel vba

0
votes

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?

2
excelvba
Possible duplicate of Better way to find last used row – Foxfire And Burns And Burns
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

2 Answers

1
votes

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

0
votes

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.