Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / September 2007

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Anthony B. - 25 Sep 2007 20:31 GMT
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
Keithlo - 25 Sep 2007 20:42 GMT
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

> 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 ***
JW - 25 Sep 2007 20:52 GMT
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
> Do you mean the first blank cell in column "A"?  Or does the entire row need
> to be validated as being blank?
[quoted text clipped - 20 lines]
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
Gord Dibben - 25 Sep 2007 20:44 GMT
Sub findbottom()
   ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
           .Offset(1, 0).Select
End Sub

Gord Dibben  MS Excel MVP

>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 ***
JW - 25 Sep 2007 20:57 GMT
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-
> Sub findbottom()
>     ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
[quoted text clipped - 9 lines]
> >
> >*** Sent via Developersdex http://www.developersdex.com ***
Ron de Bruin - 25 Sep 2007 21:27 GMT
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

Signature

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

> 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
[quoted text clipped - 28 lines]
>> >
>> >*** Sent via Developersdex http://www.developersdex.com ***
Gord Dibben - 25 Sep 2007 23:55 GMT
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

>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
[quoted text clipped - 28 lines]
>> >
>> >*** Sent via Developersdex http://www.developersdex.com ***
JW - 26 Sep 2007 02:29 GMT
> I thought about that but 90% of the time the posters want the cell below last
> cell with data.
[quoted text clipped - 35 lines]
>
> >> >*** Sent via Developersdexhttp://www.developersdex.com***

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

Regards,
-Jeff-
JW - 25 Sep 2007 20:44 GMT
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

> 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 ***
Mike H - 25 Sep 2007 20:52 GMT
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

> 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 ***
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.