I am using Office 2003 on Windows XP.

I need to find the bottom-most row in a sheet - any column - that contains
an interior color index of 35. Is a "For Each" over all cells the only/best
way?

Could someone please post example code?
Thanks much in advance

Sub test2()
Dim nLastURrow As Long
Dim i As Long, n As Long
Dim r As Range, cel As Range
Dim v

Set r = ActiveSheet.UsedRange
nLastURrow = r.Rows.Count
r.Select
For i = nLastURrow To 1 Step -1
v = r.Rows(i).Interior.ColorIndex
If IsNull(v) Then
For Each cel In r.Rows(i).Cells
If cel.Interior.ColorIndex = 35 Then
n = r.Rows(i).Row
Exit For
End If
Next
ElseIf v = 35 Then
n = r.Rows(i).Row
End If
If n Then Exit For
Next

MsgBox n ' if 0 failed to find #35

End Sub

Note - entire rows of the same format can exist outside the used range,
perhaps with #35 ?. As written this only tests within the used range.

Regards,
Peter T

"XP" wrote in message
...
I am using Office 2003 on Windows XP.

I need to find the bottom-most row in a sheet - any column - that contains
an interior color index of 35. Is a "For Each" over all cells the
only/best
way?

Could someone please post example code?
Thanks much in advance

Hi Peter,

Note - entire rows of the same format can exist outside the used range,
perhaps with #35 ?. As written this only tests within the used range.

Would not any specially formatted cell form part of the used range?


---
Regards,
Norman



"Peter T" <peter_t@discussions wrote in message
...
Sub test2()
Dim nLastURrow As Long
Dim i As Long, n As Long
Dim r As Range, cel As Range
Dim v

Set r = ActiveSheet.UsedRange
nLastURrow = r.Rows.Count
r.Select
For i = nLastURrow To 1 Step -1
v = r.Rows(i).Interior.ColorIndex
If IsNull(v) Then
For Each cel In r.Rows(i).Cells
If cel.Interior.ColorIndex = 35 Then
n = r.Rows(i).Row
Exit For
End If
Next
ElseIf v = 35 Then
n = r.Rows(i).Row
End If
If n Then Exit For
Next

MsgBox n ' if 0 failed to find #35

End Sub

Note - entire rows of the same format can exist outside the used range,
perhaps with #35 ?. As written this only tests within the used range.

Regards,
Peter T

Hi Norman,

Would not any specially formatted cell form part of the used range?

Strangely - not necessarily. When it comes to entire rows/columns the used
range appears to calculate itself in a manner known only to itself. At least
not understood by me and sometimes not even known to itself - those
occasional can't reset usedrange problems!

If say rows 10:10000 have been applied with some identical format, and the
"otherwise" last cell is somewhere above, the used range may indicate any
row from rows 10 to 10000 and the "otherwise" last column. It can depend on
what other things exist, or have existed in the sheet.

Formatting entire cells with same format does not appear to impact the used
range at all, fortunately!

Sub test3()
Worksheets.Add
Range("B:B, G:G, 3:3, 21:21").Interior.ColorIndex = 15
Range("D5,E19").Value = 1
ActiveSheet.UsedRange.Select
MsgBox ActiveSheet.UsedRange.Address(0, 0) ' B3:G21

Cells.Interior.ColorIndex = 19
ActiveSheet.UsedRange.Select
MsgBox ActiveSheet.UsedRange.Address(0, 0) ' D5:E19
End Sub

Regards,
Peter T




"Norman Jones" wrote in message
...
Hi Peter,

Note - entire rows of the same format can exist outside the used range,
perhaps with #35 ?. As written this only tests within the used range.

Would not any specially formatted cell form part of the used range?


---
Regards,
Norman



"Peter T" <peter_t@discussions wrote in message
...
Sub test2()
Dim nLastURrow As Long
Dim i As Long, n As Long
Dim r As Range, cel As Range
Dim v

Set r = ActiveSheet.UsedRange
nLastURrow = r.Rows.Count
r.Select
For i = nLastURrow To 1 Step -1
v = r.Rows(i).Interior.ColorIndex
If IsNull(v) Then
For Each cel In r.Rows(i).Cells
If cel.Interior.ColorIndex = 35 Then
n = r.Rows(i).Row
Exit For
End If
Next
ElseIf v = 35 Then
n = r.Rows(i).Row
End If
If n Then Exit For
Next

MsgBox n ' if 0 failed to find #35

End Sub

Note - entire rows of the same format can exist outside the used range,
perhaps with #35 ?. As written this only tests within the used range.

Regards,
Peter T

Hi Peter,

A very interesting anomaly and one that I was not aware of.

Thank you for bringing it to my attention.

- I almost said highlighting it!

---
Regards,
Norman

"Peter T" <peter_t@discussions wrote in message
...
Hi Norman,

Would not any specially formatted cell form part of the used range?

Strangely - not necessarily. When it comes to entire rows/columns the used
range appears to calculate itself in a manner known only to itself. At
least
not understood by me and sometimes not even known to itself - those
occasional can't reset usedrange problems!

If say rows 10:10000 have been applied with some identical format, and the
"otherwise" last cell is somewhere above, the used range may indicate any
row from rows 10 to 10000 and the "otherwise" last column. It can depend
on
what other things exist, or have existed in the sheet.

Formatting entire cells with same format does not appear to impact the
used
range at all, fortunately!

Sub test3()
Worksheets.Add
Range("B:B, G:G, 3:3, 21:21").Interior.ColorIndex = 15
Range("D5,E19").Value = 1
ActiveSheet.UsedRange.Select
MsgBox ActiveSheet.UsedRange.Address(0, 0) ' B3:G21

Cells.Interior.ColorIndex = 19
ActiveSheet.UsedRange.Select
MsgBox ActiveSheet.UsedRange.Address(0, 0) ' D5:E19
End Sub

Regards,
Peter T