Problem with Loop Macro to Hide Rows.

Hi All,

I have been trying to create a macro to hide blank rows. However I want leave single blank rows where they appear between groups of data. The single blank rows are already in the data set and the large number I want to hide are at the end of the group of data. I created the macro below and it works leaving the single blank rows however when it reaches the multiple blank rows it only hides every other blank row. Here is the code:

Please Login or Register  to view this content.

I have tried varying a few parameters to no avail and need some other ideas! Any help would be appreciated!

Cheers
James

Please read the forum rules and adjust your post accordingly.

doublepost

try this (I recommend that you step through the code, to see what values r1 and r2 are, and to check that it does as required..
also: always perform a backup before running unknown code :-)

Please Login or Register  to view this content.

Hi,

Just getting a runtime 6 overflow error and does not run. If I change the code slightly replacing -2 to 2 in the offsets it runs but then hides the single blank rows.

made a small alteration. see example.xls

(not sure what you want to accomplish with the loops in your original code tho')

Hi Wamp,

I have attached an example of what I am trying to do. I have varying amounts of data (with single blank rows in it which I want to keep) which I paste into an existing template sheet. If the data is only a small amount then I have to scroll down 500+ rows to bring up the footer before printing. I simple want to have a marco to hide the rows after the data whilst keeping my single blank rows.

i=11 as i want the loop to start in cell B11
I use the "stop" to stop the loop when it reaches a cell with "stop" in it so that the loop stops before entering the footer.
Your example works but gives a runtime error.

Cheers
James

This should work:

Please Login or Register  to view this content.