Shorten spreadsheet

[NIWANut]

Hi,

If a spreadsheet has 50 columns x 250 rows and you delete great chunks of them, say to only have 10 columns by 50 rows left, then use Print Preview, Excel shows the preview as if all the columns and rows(50x250) contained data. If the shortened spreadsheet is saved and then re-opened, Print Preview will now only preview 10 columns by 50 rows.

Does anyone know how to achieve this without going thru the save and open process? Obviously the columns and rows here are only an example and in most instances the spreadsheet is being reduced in a macro so they could be any numbers.

Thanks

George

George

 

[PBAPaul]

George

Reset your print range before doing a print preview.

If your range starts at A1 then:

Range("A1&quot.currentregion.select
ActiveSheet.PageSetup.PrintArea = Selection.Address

This will reset your print are to that of the selected area.

Hope this helps

Paul

 

[NIWANut]

Paul,

That works provided that there is no empty cells in the block we want to print - the major problem with currentregion.

I have ways to find the limits of my spreadsheet so perhaps a modification to this technique will work.


George

 

[combo]

Other two ways:

1. clear PrintArea, will be automatic:
ActiveSheet.PageSetup.PrintArea = ""

2. Work with UsedRange instead of CurrentRegion.

combo