Go to first blank cell

[Curt D.]

Does anyone know how to find the first blank cell in a column using VBA. I
am running a macro and I want it to go to the first blank cell in a column
and count the number of cells above it that are not blank.

 

[Harlan Grove]

Does anyone know how to find the first blank cell in a column using VBA.
I am running a macro and I want it to go to the first blank cell in a
column and count the number of cells above it that are not blank.

Dim fnb As Range
Set fnb = Range("X1")
If Not IsEmpty(fnb.Value) Then
Set fnb = fnb.End(xlDown)
If fnb.Row < ActiveSheet.Rows.Count Then Set fnb.Offset(1, 0)
End If

If cell X1 is blank, it's the first nonblank cell. If all cells in col X are
nonblank, this will set fnb to X65536, but you'll have to check whether it's
blank.

 

[Curt D.]

Thanks for the help Harlan, how would this be written in VBA if I want the
first blank cell in column A to count all the no blank cells above it?

 

[Harlan Grove]

Curt D. wrote...

Thanks for the help Harlan, how would this be written in VBA if I want the
first blank cell in column A to count all the no blank cells above it?

....

If you want the first blank cell in col A to become a cell with a
formula counting the preceding nonblank cells, you could try


Dim fnb As Range

Set fnb = Range("A1")

If IsEmpty(fnb.Value) Then
Set fnb = fnb.End(xlDown)

If fnb.Row = ActiveSheet.Rows.Count Then
MsgBox Prompt:="Column A is *ENTIRELY* blank. Nothing to count."
Set fnb = Nothing
End If

End If

If Not fnb Is Nothing Then
Set fnb = fnb.End(xlDown)

If fnb.Row < ActiveSheet.Rows.Count Then
fnb.Offset(1, 0).Formula = "=COUNTA(A1:" & fnb.Address(0, 0) & ")"

Else
MsgBox Prompt:="No blank cell below nonblank cells in which to
enter count."

End If

End If

 

[Curt D.]

That worked Harlon, thanks.