Remove columns if certain cell is equal to zero

[csch123]

I was given a highly complicated allocation of effort spreadsheet. I am looking to build a macro that looks within a cell. If that cell is equal to 0.00%, then I need it to delete that column, as well as the two columns prior to it. If it finds a specific cell with a value greater than 0.00%, then it would keep that column as well as the two columns prior to it.

Attachment macro summary:

Look in cell E37 (column header = Both). If value is 0.00%, then delete that row as well as column C & D. Repeat the same task in the same row with the column header = Both)

Essentially, I need the macro to look in cells E37, H37, K37, etc. (wherever the total is with a header = Both) and delete the prior two columns if that value is 0.00%

I appreciate the help if it this is something that is possible.

[csch123]

Also, if the macro could repeat on the other tabs as well, that would be helpful!

[arlu1201]

Try this macro on a test file first - You have sum totals in the last column. This shows a REF error once the columns are deleted. Let me know if you want the formulae in that column to be re-input through code. Also ensure that the word "Both" is in row 3 in each sheet of your file.

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button