Is there a quick way for deleting multiple blank columns or rows in an Excel wor

Topic

New Reply

[Ron M]

I am running Office 365 under Win 10 Pro version 20H2.

I often import CSV files and then convert them to XLSM files. The CSV file often has blank rows (e.g., missing data) or blank columns (e.g., data not required in the XLSM file).  These rows and columns are often missing because not all CSV files contain every data type or data and some contain data that is not required for the analysis.  As well, the CSV files may contain some columns and rows that are not required for the analysis.

Once I have converted these files to XLSM files, I wish to remove any data or blank rows and\or data or blank columns to condense the XLSM files.  Is there a “quick” way to do this when compared to deleting them one at a time?  Note: these rows and columns are not always contiguous.

Any insight will be greatly appreciated.  TIA. 🙂

Ron M

• This topic was modified 2 years, 2 months ago by Ron M.
• This topic was modified 2 years, 2 months ago by Ron M.

Reply | Quote

Replies

Apparently you can achieve this with PowerShell (and have it work on the structured text of the CSV file and pipe the output to the Export-Excel cmdlet).

However, for a ‘tradional’ method within Excel, have a look at this Delete multiple blank rows and columns in Excel article about using Find & Select > Special to achieve the same result.

Hope this helps…

Reply | Quote

Rick, thanks for the insight.  I haven’t reached the Powershell process yet – its on my list of things to learn after an Intro to VBA.  I will follow up with the “Delete multiple blank rows and columns in Excel” link you gave me.

Ron M

Reply | Quote

For blank rows you sort the spreadsheet. This will move the blanks to the bottom.

Specific columns can be selected by holding Ctrl down while clicking on the column head, then right click for “delete columns”.

cheers, Paul

Reply | Quote

Paul, interesting approach.  I will definitely think about this one and I will have to try it out

Ron M

Reply | Quote