PDA

View Full Version : Find Last Row for "x"



YellowLabPro
03-17-2007, 04:04 AM
This procedure works fine w/ my existing worksheet. However I now would like to change the range of the looping to a variable to handle when I add or delete rows.
I have an idea of how to find the last row, but this worksheet has a summary area below the worksheet. It is separated by one blank row, if I use this to countdown rather than xlUp, I think this will work.
The other specific is the last row in the worksheet 133 needs to be left intact. So when we find the lastrow we need to add the -1.

I need some assistance in altering the two lines:

For x = 16 To 132 Step 2
Cells(x, "K").Interior.ColorIndex = 35
Next x
For x = 17 To 131 Step 2
Cells(x, "K").Interior.ColorIndex = 37
Next x

Thanks--

YLP

mudraker
03-17-2007, 05:48 AM
find row before next blank cell in column k


iLastRow = Range("k1").End(xlDown).Row

or find row before next blank cell in column k & -1


iLastRow = Range("k1").End(xlDown).Row - 1


If your column has multiple blank cells before the row that you are trying locate this will fail.

mdmackillop
03-17-2007, 05:56 AM
this worksheet has a summary area below the worksheet
It's a Post-it stuck on the monitor frame!:rotlaugh:

I'm not totally clear what you're after, is it something like this?
y = Cells(Rows.Count, "K").End(xlUp).Row

For x = 16 To y - 2 Step 2
Cells(x, "K").Interior.ColorIndex = 35
Next x
For x = 17 To y - 3 Step 2
Cells(x, "K").Interior.ColorIndex = 37
Next x

YellowLabPro
03-17-2007, 06:54 AM
It's a Post-it stuck on the monitor frame!:rotlaugh:


Nice to keep it fun.... :-)

Thanks guys,
I am off to a woodworking show for the afternoon, I will check back and let you know what I find w/ either of the two solutions....

Thanks for the help,

YLP

mdmackillop
03-17-2007, 06:55 AM
BTW, if you have some unique text in your bottom row, you can use Find to return the row number

y = Columns(10).Find("Yelp's Total").Row