Solved: Selecting used range of each row in loop?

[Simon Lloyd]

Hi all, just on my way to bed but a 4am this morning i had a little trouble with this:
[VBA]
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
.Rows(iRow).Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft)).Interior.ColorIndex = 3
End With
Next iRow
[/VBA]it works in that it will colour every second row in amount specified, however i thought that:
[VBA]
.Rows(iRow).Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft))
[/VBA]would only allow the used range of each row i want formatted to be coloured, but it actually colours from the first cell to the last used column for the whole block!, so i tried a variation:
[VBA]
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Rows(iRow).Range(.Cells(1, iRow), .Cells(iRow, Columns.Count).End(xlToLeft)).Address
End With
Next iRow
[/VBA]but gave very odd results it gave a message showing every 4th row but for double the size of the range!, i am now trying to get this to work:
[VBA]
For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Rows(iRow).Range(.Cells(1, iRow), .Cells(.Columns.Count.End(xlToLeft).Column, iRow)).Address
End With
Next iRow
[/VBA]but cant get the syntax right, maybe its because i'm bog eyed at the moment so a shove in the right direction would be nice!

[Bob Phillips]

[vba]

For iRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row To 2 Step -2
With Sheets("Sheet2")
MsgBox .Range(.Cells(iRow, 1), .Cells(iRow, Columns.Count).End(xlToLeft)).Address
End With
Next iRow
[/vba]

[Simon Lloyd]

Thanks for the speedy reply Bob, however i already had that in the first portion of code i posted, if lets say the last row is 7 columns wide so from A23:G23 then colour that, two rows up A21:I21 colour that, two rows up A19:E19 colour that....etc each row length would be different thats what i was having trouble achieving!

[Bob Phillips]

No you didn't Simon. Check my code again, I removed one very harmful piece of redundancy.

Admittedly, I MsgBox the address, not colour the range, but that is the easy bit.

[Simon Lloyd]

Bob, many apologies, did i mention ive been working nights?, yes i see you removed iRow from the front of the range, the colouring wasn't an issue just that i couldn't display a bar graph effect it was Bl**dy annoying, just like me saying you'd posted the same as me back, i used the msgbox to prove my results first before applying the code, anyway i can have a restful sleep now. BTW why do you say the .Rows(iRow) was Very harmful?

[Bob Phillips]

I noticed that earlier Simon when I saw a post from you timed at 5:20am! I just could not work nights, I would never cope.

It is harmful because it has the effect of changing the following range statements to offseting from that row, distorting the derived range. It is not harmful per se, just harmful in that context.