Detecting and Deleting empty rows and cols in excel using win32::OLE

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I am working on large excel sheet which has huge empty rows and cols. I searched net for any code sample but in vain. I also searched the documentation in MSDN on Excel methods. Request you to show some sample code to detect and delete empty rows and cols. Thanks

Comment on Detecting and Deleting empty rows and cols in excel using win32::OLE

Replies are listed 'Best First'.
Re: Detecting and Deleting empty rows and cols in excel using win32::OLE by psini (Deacon) on Sep 19, 2008 at 13:32 UTC
Disclaimer: I don't have a Win box at hand now, so I can't verify what I'm saying. I think that if you give a "select all" command and then examine the selection object, you should be able to determine the last used column and row. After that, it's only a matter of iterating through rows and columns to find the empty ones. Rule One: "Do not act incautiously when confronting a little bald wrinkly smiling man."	[reply]
Re: Detecting and Deleting empty rows and cols in excel using win32::OLE by strat (Canon) on Sep 20, 2008 at 09:17 UTC
Hello, you can use the method `UsedRange` on a worksheet object to find the last used cell in a worksheet object. This way, you don't need to iterate over all 64K rows (or more with Excel 2007) which should save much time. But I don't know a better way to find out which lines or columns are empty than an iteration. Best regards, perl -e "s>>F>e=>y)\martinF)stronat)=>print,print v8.8.8.32.11.32"	[reply] [d/l]