Search code examples
excelvba

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

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?

Solution

  • 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.