Removing blank rows

I have a spreadsheet that pulls data from an outside source and performs
certain changes to the data through a macro I run. Once the macro is
finished, there are anywhere from 3,000 to 20,000 rows of data and the rest
of the rows (up to 65,000 plus) are blank. Normally, when you save a
spreadsheet it stops at the last row of data but for some reason (I'm
assuming the functions in my macro are to blame) the spreadsheet is saved
with all 65,000 plus rows.

Question, is there a line of code that I can include in the macro that will
delete all the blank rows at the end of the spreadsheet so that only the
rows with data will remain?

When you hit CTRL + END where does Excel take you?

Down to row 65536 or somewhere short of that?

There are always 65536 rows in a sheet but I think you want your "used range"
to be to end of data, correct?

You could modify the code to not select/paste to full columns or just reset
the used range when done.

See Debra Dalgleish's site for code to reset the used range.

http://www.contextures.on.ca/xlfaqApp.html#Unused


Gord Dibben Excel MVP

On Tue, 15 Mar 2005 18:45:21 -0500, <[email protected]> wrote:

>I have a spreadsheet that pulls data from an outside source and performs
>certain changes to the data through a macro I run. Once the macro is
>finished, there are anywhere from 3,000 to 20,000 rows of data and the rest
>of the rows (up to 65,000 plus) are blank. Normally, when you save a
>spreadsheet it stops at the last row of data but for some reason (I'm
>assuming the functions in my macro are to blame) the spreadsheet is saved
>with all 65,000 plus rows.
>
>Question, is there a line of code that I can include in the macro that will
>delete all the blank rows at the end of the spreadsheet so that only the
>rows with data will remain?
>

You could try this!



Sub Delete_Rows_Code()
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub