How do I find the next blank cell in a range?

E

EazyCure

Hey there all -
I am sure that this is terribly simple, but it has eluded me thus far. I am
trying to figure out how to find the first blank cell in a range. I am
working on creating a time sheet, and I need to be able to find the first
blank cell in a specific range of cells. I have found information on finding
the first blank cell in a single column, but this will be a two column range
that I am working with. Any help would be greatly appreciated.
 
R

Ron Coderre

I'm guessing that there's more to this story....

What do you ultimately want to do after you find the blank cell?

***********
Regards,
Ron

XL2002, WinXP
 
E

EazyCure

After finding the blank cell I am going to enter the current date and time.
In the end I am going to write a macro where with the click of a button the
current date and time will be entered into the first available cell, I just
got hung up trying to find the first available cell.
 
D

Dave Peterson

Say your range is A1:B9999

And both A9999 and B1 are the first blank cells in each column.

Which is the first blank cell in that range (A1:B9999)?

No matter what you respond, you may want to look at
..specialcells(xlcelltypeblanks).cells(1). The question is what to apply it to.

It's the equivalent of selecting a range and hitting
edit|goto|special|Blanks
and then using the first cell in that reduced range.
 
D

Dave Peterson

You can loop backwards through the columns:

Option Explicit
Sub testme01()

Dim FirstEmptyCell As Range
Dim wks As Worksheet
Dim myRng As Range
Dim iCol As Long

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("a1:B20")
End With

With myRng
For iCol = .Columns.Count To 1 Step -1
Set FirstEmptyCell = Nothing
On Error Resume Next
Set FirstEmptyCell _
= .Columns(iCol).Cells.SpecialCells(xlCellTypeBlanks).Cells(1)
On Error GoTo 0

If FirstEmptyCell Is Nothing Then
'keep looking
Else
Exit For
End If
Next iCol
End With

If FirstEmptyCell Is Nothing Then
MsgBox "No empty cells!"
Else
MsgBox FirstEmptyCell.Address
End If

End Sub

Be aware that .specialcells() only looks at the used range. I'm not sure if
that's a problem for you.
 
Top