Last Row Problems in Macros

forest2m

New Member
Joined
Sep 23, 2008
Messages
37
Hello All,
I have an issue that occurs in a couple of my macros. This code works fine:
Code:
Dim LineCount As Long, i As Long
LineCount = Range("A65536").End(xlUp).Row
For i = 1 To LineCount
'  Perform data steps here
Next i

However, I have some problems with the following:
Code:
Range("V1:V" & Range("V1").SpecialCells(xlLastCell).Row).Interior.ColorIndex = 45
or
Code:
Range("Z1:Z" & Range("Z1").SpecialCells(xlLastCell).Row).Value = " "

The problems arise when I have deleted rows in the data, then run the macros containing either of the last two examples. My understanding from reading other posts is that xlLastCell is problematic and doesn't reset immediately. How can I rewrite the last two code examples using xlUp? Any help would be wonderful.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Try

Code:
Range("V1:V" & Range("V" & Rows.Count).End(xlUp).Row).Interior.ColorIndex = 45
 
Upvote 0
VoG,
Thanks for your reply. I tried your code, and I am no longer getting highlighted cells below the end of the data when I have deleted rows prior to running the macro.

However, I run into problems if the data in Column V doesn't go all the way to the last row of data. There might be blanks in some of the cells. If some of those blanks are at the end of the data, the code only works up to the last cell with data in Column V only.

I also tried:
Code:
Range("Z1:Z" & Range("Z" & Rows.Count).End(xlUp).Row).Value = " "
for my second example, but I only get a space in the very first row. There is no data in Column Z before the macro runs.

Column A always has data in every row, all the way to the last row of data. Can I somehow base the end of the data on Column A and apply it to these other columns? I just tried this loop and it inserts a space in every row of Column Z:
Code:
Dim LineCount As Long, i As Long
LineCount = Range("A65536").End(xlUp).Row
For i = 1 To LineCount
    Cells(i, 26).Value = " "
Next i
Any ideas on how to do this without a loop? Can I incorporate the LineCount variable with the code you posted to accomplish this?
 
Upvote 0
Try

Code:
Range("Z1:Z" & LineCount).Value = " "

That is putting a space in the cells. If you actually want to clear them:

Code:
Range("Z1:Z" & LineCount).ClearContents
 
Upvote 0
Thank you both for replying. I like both solutions. For the macros I have been using, slomobonobo's code seems simplest, since I don't need to code any variables. Thanks again for such quick replies!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top