Delete entire column if all cells in a certain range are empty

**Andula** · 10-26-2011, 05:06 AM

I'm not experienced with macros but I'm trying to automate a spreadsheet formatting process to save me a lot of time. One of the functions I need to perform is to delete columns where all cells within a certain range are empty. So, in columns C to FZ, if cells 3 to 1000 are all empty, delete the entire column.

Can anyone help me with this?

**Domski** · 10-26-2011, 05:49 AM

Hi,

Try this:

Please Login or Register  to view this content.

Dom

**Richard Buttrey** · 10-26-2011, 05:51 AM

Hi,

Create a helper row 1 and use =IF(SUM(C4:C1001)=0,"delete","keep")

Now use a macro along the lines of

Please Login or Register  to view this content.

Regards

**nilem** · 10-26-2011, 06:04 AM

another option:

Please Login or Register  to view this content.

**Andula** · 10-26-2011, 06:07 AM

Thanks Dom, that works nicely

**Andula** · 10-26-2011, 06:12 AM

Nilem - even better, thanks

**Richard Buttrey** · 10-26-2011, 06:33 AM

Originally Posted by Richard Buttrey

Hi,

Create a helper row 1 and use =IF(SUM(C4:C1001)=0,"delete","keep")

Now use a macro along the lines of

Please Login or Register  to view this content.

Regards

That formula should of course be put in C1 and copied across to FZ1 and the Range in the macro refer to C1:FZ1 and not my test range of G1:L1

Regards