Blank Rows at end of Worksheet

  • Thread starter Thread starter Neil
  • Start date Start date
N

Neil

I've written a custom macro that manually sorts the rows
in my spreadsheet into custom sections. I use the Cut and
Paste methods to move rows from the bottom of my sheet to
the appropriate section. However, after I run the macro I
end up with additional blank rows at the end of the sheet.
I tried selecting the rows and deleting them, but they
still show up. This causes a problem when printing since
the blank rows get included. If I run the macro 10+ times,
I end up with thousands of blank lines at the end. Any
idea on how to get rid of these blank lines?
 
Have you tried resetting the usedrange. It might work.

Try
set rngTemp = activesheet.usedrange
set rngTemp = nothing
msgbox(activesheet.usedrange) ' to test

Alternatively, delete the excess rows and reset your print area.

Robin Hammond
www.enhanceddatasystems.com
Check out our XspandXL add-in
 
Using the UsedRange method helped remove the blank rows when printing,
but they still exist on the sheet and continue growing. Instead of
having 200 rows of data, the sheet thinks it's got 1600 rows of data. It
makes using the scroll bar near impossible.

I tried simply deleting the blank rows manually and via code, but
neither had an impact. Any way to reset the rows so the scroll bar on
the right-hand side doesn't think there is data in the blank rows?
 
Back
Top