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

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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

"Anthony B." wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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


Gord Dibben MS Excel MVP

On Tue, 25 Sep 2007 12:31:12 -0700, Anthony B. wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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

Anthony B. wrote:
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

*** Sent via Developersdex http://www.developersdex.com ***

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

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
"Anthony B." wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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

"Anthony B." wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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-
Gord Dibben wrote:
Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 Sep 2007 12:31:12 -0700, Anthony B. wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***

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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"JW" wrote in message ps.com...
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-
Gord Dibben wrote:
Sub findbottom()
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Select
End Sub


Gord Dibben MS Excel MVP

On Tue, 25 Sep 2007 12:31:12 -0700, Anthony B. wrote:

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

*** Sent via Developersdex http://www.developersdex.com ***