Blank portions of the worksheet after the data
 

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

--
David
-----------------------------------------------------------------------
DavidW's Profile: http://www.excelforum.com/member.php...fo&userid=3263
View this thread: http://www.excelforum.com/showthread.php?threadid=52960

Blank portions of the worksheet after the data
 

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

Blank portions of the worksheet after the data
 

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

Blank portions of the worksheet after the data
 

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.


--
DavidW
------------------------------------------------------------------------
DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630
View this thread: http://www.excelforum.com/showthread...hreadid=529601