Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Empty Cell in Column
Hope this question hasn't come up to often. I can't find where it was asked
before so here goes. I would like to find the next empty cell below my last entry. What VBA code would I use to accomplish this. Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Empty Cell in Column
Hi Steve,
There may be a number of ways to accomplish this but I'll send you a couple of solutions that immediately occur to me. I'm first going to assume that you're making cell entries in a single column (if I'm wrong, please let me know). Let's say that your data entries begin in cell A6. You want your code to locate the next data entry cell in column A (the first blank cell below A6). One way to do this is: Method 1: Find the last possible data entry cell in the column then do and "End Up" Sub FindNextCell() 'Locate the next data entry cell in data entry column A If Range("A65536").Value = "" Then Range("A65536").Select Selection.End(xlUp).Select Else MsgBox "You have filled the data entry column" End If End Sub Method 2: Loop through the data entry cells until you find the first blank Sub FindNextCell() 'Locate the next data entry cell in data entry column A Dim FirstCell As String Dim i As Integer FirstCell = "A6" Range(FirstCell).Select Do Until ActiveCell.Value = "" If ActiveCell.Value = "" Then Exit Do Else ActiveCell.Offset(1, 0).Select End If Loop End Sub "caldog" wrote: Hope this question hasn't come up to often. I can't find where it was asked before so here goes. I would like to find the next empty cell below my last entry. What VBA code would I use to accomplish this. Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Empty Cell in Column
simple: ActiveCell.End(xlDown).Offset(1, 0).Select or, if you want to make it complicated :-) Range(ActiveCell, Cells(65536, ActiveCell.Column)) _ .SpecialCells(xlCellTypeBlanks).Select ActiveCell.Selec -- Pointles ----------------------------------------------------------------------- Pointless's Profile: http://www.excelforum.com/member.php...fo&userid=3086 View this thread: http://www.excelforum.com/showthread.php?threadid=50804 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Empty Cell in Column
Couple of elaborating comments on Pointless's suggestions.
If you are sitting at the bottom of the UsedRange when you do this, both of these methods will raise an error. ActiveCell.End(xldown) would go to the last row in the column, then doing an offset of 1 more row, raises the error Specialcells will only look at the used range, so if you are at the bottom of the usedrange, from a specialcells perspective, all the empty cells below that are virtual and do not exist. This would result in an error. Either could be corrected by making a check of the next cell below. if isempty(activecell.Offset(1,0)) then ActiveCell.Offset(1,0).Select else ' one of Pointless's methods end if The above will fail if you are sitting on row 65536, but I would expect this to be unlikely. Another consideration with ActiveCell.End(xldown).Offset(1,0).Select is if you are sitting in a blank cell with two or more contiguous cells that are not empty below you, then This would select the second filled cell. Since you said below my last entry, I assume this would not be the case. -- Regards, Tom Ogilvy "Pointless" wrote in message ... simple: ActiveCell.End(xlDown).Offset(1, 0).Select or, if you want to make it complicated :-) Range(ActiveCell, Cells(65536, ActiveCell.Column)) _ SpecialCells(xlCellTypeBlanks).Select ActiveCell.Select -- Pointless ------------------------------------------------------------------------ Pointless's Profile: http://www.excelforum.com/member.php...o&userid=30862 View this thread: http://www.excelforum.com/showthread...hreadid=508042 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Next Empty Cell in Column
Thanks folks that is exactly what I was looking for.
Steve "Pointless" wrote: simple: ActiveCell.End(xlDown).Offset(1, 0).Select or, if you want to make it complicated :-) Range(ActiveCell, Cells(65536, ActiveCell.Column)) _ .SpecialCells(xlCellTypeBlanks).Select ActiveCell.Select -- Pointless ------------------------------------------------------------------------ Pointless's Profile: http://www.excelforum.com/member.php...o&userid=30862 View this thread: http://www.excelforum.com/showthread...hreadid=508042 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding next empty empty cell in a range of columns | Excel Programming | |||
Finding the first empty cell in a column | Excel Programming | |||
Finding last non-empty column in row... | Excel Programming |