Finding Next Empty Cell in a Range

[Stilltrader47]

My worksheet is set-up for 12 month price tracking. The entry range is 12
rows (months) by 31 columns (days). How can I write a macro that will locate
the cursor to the next empty cell in the range? What I am looking for is an
efficient "find" command. I would prefer to execute by ctl-(letter) if
possible.

Thanks for helping.

 

[JLatham]

Need more information - what last/empty cell are you looking for? Next empty
one in a row or column? Do we need to consider what the current month or day
is?

While we're figuring that out, here's code with 3 separate 'searches' in it,
pick the one you like or wait for another to come along.

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
Exit For
End If
Next
'
'this searches left-to-right then down
'results should be same as above
exitFlag = False
For RP = 2 To 13
For CP = Range("B2").Column To Range("AF13").Column
If IsEmpty(Cells(RP, CP)) Then
MsgBox "Empty cell at " & Cells(RP, CP).Address
Cells(RP, CP).Select
exitFlag = True
Exit For
End If
Next
If exitFlag Then
Exit For
End If
Next
'
'this searches down then left-to-right
exitFlag = False
For CP = Range("B2").Column To Range("AF13").Column
For RP = 2 To 13
If IsEmpty(Cells(RP, CP)) Then
MsgBox "Empty cell at " & Cells(RP, CP).Address
exitFlag = True
Cells(RP, CP).Select
Exit For
End If
Next
If exitFlag Then
Exit For
End If
Next

End Sub

 

[FSt1]

hi
this code is ugly but it works. think i've been up too long.
i assumed that you have a header row in row 1 and data entry starts in row 2.
i assumed that you input start at A2 across to AE2 then down to A3.
if my assumption are not correct then code may be useless.
code goes in a standard module.
Alt+F11.
in the project window(far left), expand the project(file)
if no module then on the vb menu bar>insert>module
paste the code in the code window(far right)
keyboard short cut.
on the menu bar>tools>macro>macro
highlight the macro
click the options button.
enter shortcut. ok out.
Sub FindNextEmptyCell()
Dim r, ro As Range
Dim c As Long
c = 1
Set r = Range("A2")
Do While Not IsEmpty(r)
Set ro = r.Offset(0, 1)
If c > 30 Then
Set r = r.Offset(1, -30)
Set ro = r.Offset(0, 1)
c = 1
End If
If Not IsEmpty(r) Then
Set r = ro
c = c + 1
End If
Loop
r.Select
End Sub

regards
FSt1

 

[FSt1]

on second thought, ignore this post.
i thought of something that will make the code not work.
like i said. been up to long. brain not working.
sorry.
FSt1

 

[Rick Rothstein]

This may be what you are looking for....

Sub SelectNextEmptyCell()
On Error Resume Next
Range("B2:AF13").SpecialCells(xlCellTypeBlanks)(1).Select
End Sub

However, if I am right, you could end up picking the wrong cell at the end
of short months (Feb 29th of a non-leap year, April 31st, etc.).

 

[ozgrid.com]

Try along the lines of;

Sub FindBlank()
Dim rBlank As Range

On Error Resume Next
Set rBlank = _
Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)(1, 1)
On Error GoTo 0

If Not rBlank Is Nothing Then
MsgBox "Next blank cell in your table is " & _
rBlank.Address
Else
MsgBox "No blanks in range: " & _
Range("A1").CurrentRegion.Address
End If
End Sub

 

[helene and gabor]

Hello,

I have carried Rick Rothstein's algorithm for considering the length of
various months.
The program finds the next empty cell for each month, not selecting April 31
etc.
The program is rather lengthy because for each month the empty cell has to
be found before applying Mr. Rothstein algorithm for moving the cursor to
that cell.
I did the project as part of my learning the VBA.
The program is available, but it's not as simple as the requester has
expected.

Best Regards,

Gabor Sebo

 

[Rick Rothstein]

In looking over my code, I'm not completely sure it selects the correct cell
all the time. Here is a different macro that also properly accounts for the
number of days in each month - just set the Jan1st range variable to the
cell corresponding to January 1st (in the Set statement) and the code will
do the rest (I assumed the January 1st cell is B2 in my code)...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

 

[Rick Rothstein]

I just posted a new macro (in response to my previous posting) that you may
find interesting... it properly handles the end of month transitions and it
is not what I would call "lengthy" code.

 

[helene and gabor]

I have used JLatham's program to consider the number of days each month.

best regards,

Gabor Sebo
-----------------------------------------------------------------------------------------------------------
'JLatham's program expanded 4/17/2010
'TABLE IN B2: AF13

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
'MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
m = anyCell.Address


If m = "$AD$3" Or m = "$AE$3" Or m = "$AF$3" Or m = "$AF$5" Or m =
"$AF$7" Or m = "$AF$10" Or m = "$AF$12" Then
' MsgBox "NONO" & m
' MsgBox " Caught"
GoTo rest
End If

Exit For
End If
rest:
Next
'
'this searches left-to-right then down
'results should be same as above

End Sub

 

[helene and gabor]

I have used JLatham's program to consider the number of days each month.

best regards,

Gabor Sebo
-----------------------------------------------------------------------------------------------------------
'JLatham's program expanded 4/17/2010
'TABLE IN B2: AF13

Sub FindFirstEmptyCell()
Dim RP As Integer
Dim CP As Integer
Dim seekRange As Range
Dim anyCell As Range
Dim exitFlag As Boolean

Set seekRange = ActiveSheet.Range("B2:AF13") ' 12 x 31
'this just gets the first empty cell it finds
'pretty much a search by row
For Each anyCell In seekRange
If IsEmpty(anyCell) Then
'MsgBox "Empty cell at " & anyCell.Address
anyCell.Select
m = anyCell.Address


If m = "$AD$3" Or m = "$AE$3" Or m = "$AF$3" Or m = "$AF$5" Or m =
"$AF$7" Or m = "$AF$10" Or m = "$AF$12" Then
' MsgBox "NONO" & m
' MsgBox " Caught"
GoTo rest
End If

Exit For
End If
rest:
Next
'
'this searches left-to-right then down
'results should be same as above

End Sub

 

[Stilltrader47]

J, Good question. And thanks for your continued review of my issue. The
current date is relevant, here's why. I will not be populating every cell in
the range with a price value. Days will be skipped, meaning cells in the
range will be left empty. Below is a brief example to help illustrate.

April
(Col) R S T U V
W
13 4/17/10 4/18/10 4/19/10 4/20/10 4/21/10 4/22/10
14 5.00 4.75


See that for April, the 19th, 20th and 21st do not have a price value
entered. So when the macro is run on 4/22, it should really be looking for
the next empty cell in the range equla to or approximate to the current date.
Like Row 13 above, dates will be pre-populated for all 12 months.

I hope this better explains what I am looking for. Thanks Tom

 

[Stilltrader47]

Rick, where did you post the macro? Thanks - Tom

 

[Rick Rothstein]

It was in the response to my own posting. This is the code I posted there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding to
January 1st.

 

[Stilltrader47]

Thanks Rick I am going now to run it

It was in the response to my own posting. This is the code I posted there...

Sub SelectNextEmptyCell()
Dim LastRow As Long, LastDay As Long, EndOfMonth As Long, Jan1st As Range
Set Jan1st = Range("B2")
LastRow = Jan1st.EntireColumn.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
LastDay = Cells(LastRow, Jan1st.Column).End(xlToRight).Column
EndOfMonth = Day(DateSerial(Year(Now), LastRow - Jan1st.Row + 2, 0))
If LastDay - Jan1st.Column + 1 < EndOfMonth Then
Cells(LastRow, LastDay + 1).Select
Else
Cells(LastRow + 1, Jan1st.Column).Select
End If
End Sub

Don't forget to set the Jan1st Range variable to the cell corresponding to
January 1st.

--
Rick (MVP - Excel)




.

 

[Stilltrader47]

Rick - I could not get it to run. I attached ctl-d to launch it. It should
have went to Jan1st, right? If your interested I'll send you the file, would
appreciate. Tom

 

[Rick Rothstein]

Yes, you can send the file to me.... just remove the NO.SPAM stuff from my
email address.

 

[Stilltrader47]

Rick - I sent the file and a results script to (e-mail address removed) and the
email was returned by MAILER-DAEMON (not found). I have the file ready to
provide to you for review. Please advise here, or (e-mail address removed)
Thanks - Tom

 

[Rick Rothstein]

Thank you SO MUCH for posting my "unmunged" email address in this open
newsgroup where all the spambots can find it and add it to their spam lists.
I am really SO HAPPY you did that for me. Exactly why do you think I post my
email address with the NO.SPAM stuff in there... for my health?

 

[Stilltrader47]

Rick, My apology. It was not my intent to expose your id for malicious
purposes. I was too focused on my macro issue, and need to resolve. I
should have been more observant.