How to remove non printable area from excel and save as file

%3CLINGO-SUB%20id%3D%22lingo-sub-3425845%22%20slang%3D%22en-US%22%3EHow%20to%20remove%20non%20printable%20area%20from%20excel%20and%20save%20as%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3425845%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20need%20one%20help%20to%20save%20existing%20file%20which%20have%20non%20printable%20area%20%26amp%3B%20I%20want%20to%20save%20as%20file%20without%20non%20printable%20area.%3C%2FP%3E%3CP%3EI%20also%20have%20linking%20between%20non%20printable%20area%20to%20printable%20area%20so%20while%20saving%20as%20file%20that%20need%20to%20do%20as%20paste%20special%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20sample%20file.%20Excel%20have%20multiple%20sheets%20where%20this%20needs%20to%20be%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20someone%20have%20any%20easy%20way.%20I'm%20trying%20to%20do%20it%20with%20ASCII%20code%20but%20not%20able%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESumit%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3425845%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3426035%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20non%20printable%20area%20from%20excel%20and%20save%20as%20file%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3426035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098609%22%20target%3D%22_blank%22%3E%40Sumit_0505%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFile%20won't%20open%20probably%20corrupt%20or%20associated%20macros%20that%20I%20won't%20open.%3CBR%20%2F%3ENevertheless%2C%20here%20is%20a%20macro%20to%20try%20out%20for%20yourself...not%20tested.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20MacroCopy()%0AWorkbooks.Add%0AWindows(%22Filename_Source.xls%22).Activate%0ASheets(%22sheet_name_source%22).Select%0ASheets(%22TableName_Target%22).Copy%20Before%3A%3DWorkbooks(%22Book1%22).Sheets(1)%0ACells.Select%0ASelection.Copy%0ASelection.PasteSpecial%20Paste%3A%3DxlPasteValues%2C%20Operation%3A%3DxlNone%2C%20SkipBlanks%20_%0A%3A%3DFalse%2C%20Transpose%3A%3DFalse%0AWindows(%22Filename_Source.xls%20%22).Activate%0Aend%20sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20I%20could%20help%20you%20with%20these%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel-blog%2Fmeet-niko-chatzoudis-excel-forum-contributor%2Fba-p%2F2941385%22%20target%3D%22_blank%22%3ENikolinoDE%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi,

I need one help to save existing file which have non printable area & I want to save as file without non printable area.

I also have linking between non printable area to printable area so while saving as file that need to do as paste special values.

 

Attached is sample file. Excel have multiple sheets where this needs to be done.

 

Let me know if someone have any easy way. I'm trying to do it with ASCII code but not able to do.

 

Regards,

Sumit

1 Reply

@Sumit_0505 

File won't open probably corrupt or associated macros that I won't open.
Nevertheless, here is a macro to try out for yourself...not tested.

Sub MacroCopy()
Workbooks.Add
Windows("Filename_Source.xls").Activate
Sheets("sheet_name_source").Select
Sheets("TableName_Target").Copy Before:=Workbooks("Book1").Sheets(1)
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Filename_Source.xls ").Activate
end sub

 

Hope I could help you with these information.

 

NikolinoDE

I know I don't know anything (Socrates)