Go to first blank cell
 

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.

"Curt D." <Curt wrote...
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.

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" wrote:

"Curt D." <Curt wrote...
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. 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

That worked Harlon, thanks.

"Curt D." wrote:

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.