Finding the first blank cell in a row..in column "A"

[Anthony B.]

I need help to find the first blank row in column A and need to select
that cell. Any help would be highly appreciated.

AB

 

[Guest]

Do you mean the first blank cell in column "A"? Or does the entire row need
to be validated as being blank?

If it's just the first blank cell, you could use this:

Range("A1").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1,0).Select
Loop

When that code finishes, your cursor will be in the first blank cell.

Hope this helps.

Keith

Keith

 

[Gord Dibben]

Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub


Gord Dibben MS Excel MVP

 

[JW]

To do this via keyboard, select A1 and press End followed by the down
arrow key twice.

via VBA:
Sub oneWay()
Columns("A:A").Find(what:="", LookAt:=xlWhole).Select
End Sub

Sub anotherWay()
Cells(1, 1).End(xlDown).Offset(1, 0).Select
End Sub

 

[JW]

Keith, I would not recommend using a Loop to accomplish this. On a
spreadsheet with 50,000+ lines, that loop could take quite a while to
process. Now, I don't know that the OP is dealing with a spreadsheet
that large, but the potential is there, so a Loop wouldn't be the most
effecient method.

That in mind, If A2 was the first empty cell, the second VBA code I
posted would result in an error because A65536 would be determined and
then incremented by one, which would cause an error. So, to get
around that, VBA code option 1 that I posted would probably be best.
Or option 2 could be modified like below.
Sub anotherWay()
If IsEmpty(Cells(1, 1).Offset(1, 0)) Then
Cells(1, 1).Offset(1, 0).Select
Else
Cells(1, 1).End(xlDown).Offset(1, 0).Select
End If
End Sub

 

[Guest]

2 ways

Sub sonic()
row1 = ActiveSheet.UsedRange.Rows.Count + 1
Row = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End Sub


1st is empty roew and 2nd empty cell coulmn A

Mike

 

[JW]

Gord, The code you posted would be used to select the next cell below
the last cell with data in it. The OP said that they wanted to find
the first blank cell in column A, which wouldn't necessarily be the
lone below the last row.
Example:
Row1 A
Row2 B
Row3
Row4 D
Row5

According to what the OP is asking for, I would believe that Row3
would need to be selected, not Row5. Now, the OP could certainly have
meant that they wanted to select the cell below the last used one.
Not trying to step on any toes here, but just wanted to make sure that
the OP gets the result they are after.

Regards,
-Jeff-

 

[Ron de Bruin]

Another way for the first blank cell

Sub testing()
On Error GoTo BodemUp
Columns("A").Cells.SpecialCells(xlCellTypeBlanks).Cells(1).Select
Exit Sub
BodemUp: Cells(Rows.Count, "A").End(xlUp)(2).Select
End Sub

 

[Gord Dibben]

I thought about that but 90% of the time the posters want the cell below last
cell with data.

Only OP knows for sure and now has a phlethora of code options.


Gord

 

[JW]

I thought about that but 90% of the time the posters want the cell below last
cell with data.

Only OP knows for sure and now has a phlethora of code options.

Gord

Plethora. I've always loved that word. Reminds me of el Guapo in The
Three Amigos. lol

Regards,
-Jeff-