I modified your code to show the range I would like to find if the rows had
data as follows:
Row1: aaa
Row2:
Row3:
Row4:
Row5: bbb

The following code would find a range of Row1 thru Row4.
'======
Sub findEmptyRng()
Dim lstRw As Variant
Dim FirstEmpty As Variant
Dim LastEmpty As Variant

lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2").Activate
With ActiveSheet.Range("A3:A" & lstRw)
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Activate
Loop
FirstEmpty = ActiveCell.Offset(-1, 0).Address
LastEmpty = ActiveCell.End(xlDown).Offset(-1, 0).Address
MsgBox "Empty Range is " & FirstEmpty & ":" & LastEmpty
End With
End Sub
'======

This code ends on the first range it finds? there can be many ranges in my
workbook.
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"JLGWhiz" wrote:

This is a little antiquated but it will find the first empty cell and the
last empty cell in a consecutive series of empty cells, which appears to be
what you are after. If you want the last empty cell in a non-consecutive
range of empty cells, that would take a little more doing.

Sub findEmptyRng()
lstRw = Cells(Rows.Count, 1).End(xlUp).Row
Range("A2").Activate
With ActiveSheet.Range("A2:A" & lstRw)
Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Activate
Loop
FirstEmpty = ActiveCell.Address
LastEmpty = ActiveCell.End(xlDown).Offset(-1, 0).Address
MsgBox "Empty Range is " & FirstEmpty & ":" & LastEmpty
End With
End Sub

"Rick S." wrote:

In the code below I am able to see the cell.address of cells with values
(true) and cells without values (false). Of this I can't seem to capture
when the last empty cell occured so I can set a range of first cell with a
value and last cell without a value.

As an example (Column A only); row1 would be true, then row 5 would be true
all rows in between would be false. How can I get the range of row 1 thru
row 4?

I initially thought I would get the address of the last cell before finding
a cell that is true again.
So much for that theory.
'======
Sub RowCount()

lrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ' count used rows in
Column A

For Each c In ActiveSheet.Range("A3:A" & lrow)
If c < "" Then 'if "c" is something get cell address
vNameCellAddress = c.Address 'get cell address of cell with a value
MsgBox "True" & " " & vNameCellAddress 'for testing
End If
If c = "" Then
vEmptyCellAddress = c.Address 'get cell address of cell with a value
MsgBox "False" & " " & vEmptyCellAddress 'for testing
End If
MsgBox vNameCellAddress & " " & vEmptyCellAddress
Next
End Sub
'======

Thanks in advance
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007