Blank portions of the worksheet after the data

[DavidW]

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

Thank

 

[Martin]

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

 

[MSweetG222]

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

 

[DavidW]

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.