Locate last row containing ColorIndex 35
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
|