Remove blank columns

I have a spreadsheet with app. 20,000 rows and 102 (CX) columns. I need help writing a macro that will start at column A and check to see if all the cells in that column starting at row 2 are empty. If all cells are empty I would like to delete the column.

The issue I'm having wrapping my head around this is that if I delete column A and everything shifts to the left and I now go to column 2 I am missing one column that may need to be deleted.

The first row is field names and all 102 have data in them. I would like to start at row 2.

I am using excel 2007.

Thanks

Two suggestions:

1) Set your loop to run backwards so it is starting with the last column and moving to the left. This way as columns are deleted it has no effect on the count of the position you're in as you continue.

2) Do not delete anything until the end - instead set a range as you go memorizing the columns to delete, then delete them all at once at the end. This is how I would do it. (Using Mick's suggested column evaluation technique)

Please Login or Register  to view this content.

Row 1 is not causing the problem, you're misreading what the macro is trying to do as it evaluates each column. Anyway, I didn't test Mick's evaluation technique since he's pretty sharp, so the error could be there or something else I can't picture at the moment.

Post up a sample workbook with the macro installed and let me take a look.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

It appears Mick's evaluation technique wasn't working properly for your data, so here's the macro with my standard technique instead, it will delete any column that has nothing in it after row 1:

Please Login or Register  to view this content.