Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
only deletes first row with a blank cell in column "M" | Excel Programming | |||
only deletes first row with a blank cell in column "M" con't | Excel Programming | |||
FIND is not "finding" a number in a column which contains the numb | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |