first Occurrence Of Non Blank Cell vba

Question

Hi, I am trying to write VBA code which works on my Excel sheet.

Range("A65536").End(xlUp).Row gives me the row number of the last non blank cell. similarly i am trying to get the row number of the first non blank cell in that particular column. Thanks in advance. madhu

Answer 1

You can use the same function, just check to see if the first row is blank.

Dim firstRow as Integer

if ActiveSheet.Range("A1").value = "" then
    firstRow = ActiveSheet.Range("A1").End(xlDown).Row
else
    firstRow = 1
end if

if the first row is not blank and you use the End(xlDown) call you won't get the first non-empty cell you will get the last non-empty cell before the first blank cell.

Answer 2

You can also use the IsEmpty(cell) function, which returns a boolean true/false.