Blank portions of the worksheet after the data

How would I delete the blank pages of a worksheet that remain after the cells that contain data? I have a macro that reads data from an unformatted worksheet into another formatted worksheet. The amount of data coming into the spreadsheet varies. When I run a report with a small amount of data after running one with a large amount of data there are a lot of empty pages. I can set the cells to blank before filling the spreadsheet, IE:Worksheets("Rebate").Range("A2:J7000") = "". I'm just wondering if I can delete the remaining empty cells. I want to do this in the macro.

Thanks

Sub ClearExtraCells()
Range("A1").CurrentRegion.Select
Selection.Offset(Selection.Rows.Count).Resize(1, 1).Select
Range(ActiveCell, Range("A65536")).EntireRow.Clear
Range("A1").CurrentRegion.Select
Selection.Offset(0, Selection.Columns.Count).Resize(1, 1).Select
Range(ActiveCell, Range("IV1")).EntireColumn.Clear
End Sub

"DavidW" wrote:

>
> How would I delete the blank pages of a worksheet that remain after the
> cells that contain data? I have a macro that reads data from an
> unformatted worksheet into another formatted worksheet. The amount of
> data coming into the spreadsheet varies. When I run a report with a
> small amount of data after running one with a large amount of data
> there are a lot of empty pages. I can set the cells to blank before
> filling the spreadsheet, IE:Worksheets("Rebate").Range("A2:J7000") =
> "". I'm just wondering if I can delete the remaining empty cells. I
> want to do this in the macro.
>
> Thanks
>
>
> --
> DavidW
> ------------------------------------------------------------------------
> DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630
> View this thread: http://www.excelforum.com/showthread...hreadid=529601
>
>

DavidW - Instead of posting your data directly to the "formatted" worksheet,
consider making a copy of the "formatted" worksheet and pasting your data to
the "formatted copy". That way there is no "remembering" the previous print
job.

Make sure your "source formatted" worksheet is as small as possible. While
on the worksheet, press "END" then "HOME" to located the last remembered
cell. If this is position is acceptable, then your are okay. If not, then
recreate your "source formatted" worksheet.

As a side note: I find that shading is a problem for me. Even if you remove
it (by "unshading" or deleting rows/columns, Excel still rembembers that
those cells were once shaded).

Hope this suggestion helps.

Thx
MSweetG222

Thanks, Martin and MSweet. Both suggestions make sense, but it looks like I'll have to wait until tomorrow to try them. I'm swamped with other things now.