loop help
 

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

loop help
 

Is this what you're wanting?

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.Value < "" And c.Offset(1, 0).Value = "" 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.Value = "" And c.Offset(1, 0).Value < "" Then
vemptycelladdress = c.Address 'get cell address of cell with a
value
MsgBox "False" & " " & vemptycelladdress 'for testing
End If
If vnamecelladdress < "" And vemptycelladdress < "" Then
MsgBox vnamecelladdress & " " & vemptycelladdress
vnamecelladdress = ""
vemptycelladdress = ""
End If
Next
End Sub

Ken Johnson

loop help
 

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

loop help
 

I'm having trouble figuring out exactly what you want. Lets pretend this is
the contents of Row A..

Row 1: aaa
Row 2: bbb
Row 3: ccc
Row 4:
Row 5: ddd
Row 6: eeee
Row 7:
Row 8:
Row 9: fff
Row 10: ggg
Row 11:
Row 12:
.....
.....

Everything after row 11 is empty. Which row did you want to find?

Rick


"Rick S." wrote in message
...
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

loop help
 

Rows 3 and 4 would be a range, rows 6 thru 8 would be a range and rows 10
thru 12 would be a range.
I did forget to mention the last row I want to count would have "END" in it.
So in your example Row 13 would be "END" (no quoates). At that point I
would offset up one cell.

Instead of having the same text for each row:
Row 1: aaa
Row 2: bbb
Row 3: ccc
Row 4: ccc
Row 5: ddd
Row 6: eeee
Row 7: eeee
Row 8: eeee
Row 9: fff
Row 10: ggg
Row 11: ggg
Row 12: ggg
Row 13: END

The rows have been created as in your example.

Please excuse the confusion, it was a frustrating day.
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"Rick Rothstein (MVP - VB)" wrote:

I'm having trouble figuring out exactly what you want. Lets pretend this is
the contents of Row A..

Row 1: aaa
Row 2: bbb
Row 3: ccc
Row 4:
Row 5: ddd
Row 6: eeee
Row 7:
Row 8:
Row 9: fff
Row 10: ggg
Row 11:
Row 12:
.....
.....

Everything after row 11 is empty. Which row did you want to find?

Rick


"Rick S." wrote in message
...
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

loop help
 

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

loop help
 

This appears to do what I was attempting (Live test on backup worksheet)!
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007



"Ken Johnson" wrote:

Is this what you're wanting?

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.Value < "" And c.Offset(1, 0).Value = "" 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.Value = "" And c.Offset(1, 0).Value < "" Then
vemptycelladdress = c.Address 'get cell address of cell with a
value
MsgBox "False" & " " & vemptycelladdress 'for testing
End If
If vnamecelladdress < "" And vemptycelladdress < "" Then
MsgBox vnamecelladdress & " " & vemptycelladdress
vnamecelladdress = ""
vemptycelladdress = ""
End If
Next
End Sub

Ken Johnson

loop help
 

Thanks to all who contributed!
Ken Johnson, Thank you!
--
Regards

VBA.Noob.Confused
XP Pro
Office 2007