MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro that will continue down the spreadsheet


Posted by Karon on January 16, 2002 12:56 PM

I have a hugh spreadsheet that contains 256 pages. The spreadsheet has been downloaded from a CD and at the top of every "new page" it has 4 lines pasted into the spreadsheet. I want to run a macro that does this:
Find - Page
Highlights the 4 lines, (the first line is the one page is on)
Deletes those 4 lines, then
Goes to the next "page" and does the same thing.


Posted by DK on January 16, 2002 1:01 PM

Why don't you select all sheets (right click any worksheet tab and choose Select All Sheets) and then delete the 4 rows. The 4 rows will be deleted on all sheets.

If you really want a macro then please repost.

D

Posted by Armand on January 16, 2002 1:42 PM

If the lines are empty you can use this macro below to delete the blank lines. It works well. To delete the rows that have the word "page", you can sort the spreadsheet and then select all the rows that have that word and delete them.


Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub

Posted by karon on January 16, 2002 1:49 PM

The data is all on one sheet (tab). It encompasses A1:L4021. There are no blank lines and the data is divided up between regions so I can't just sort and delete.