Deleting columns based on cell value

[Reapz]

Hi all,

I'm hoping one of you guys can help me out here. I have a spreadsheet (attached is a mock version) where I need to delete columns based on which cells are empty.

On the 'helper' tab, cell A5 (with the help of other formulas) identifies which cells are blank on the 'fullschedule' tab. I then had to write each macro to suit each combination of cells that were empty, but the number of combinations was getting out of control.

So for example, if A5 on the 'Helper' tab identifies that cells 'CILNPX' on the 'Fullschedule' tab are empty (which it has in the attached), how can I get columns CILNPX on the 'FullSchedule' tab to delete when the macro runs?

Thank you in advance for your time, I've looked around and can't find much to help me on this.

[Leith Ross]

Hello Reapz,

Excel formula macros can not delete rows or columns. This can only be done using a VBA macro.

Unfortunately, VBA macros will not run when formulas are recalculated. They can run automatically when the workbook is opened, a cell's contents are changed by the user, a button is clicked, or the user runs the macro using the Macro Dialog.

Will any of these options work for you?

[:) Sixthsense :)]

Even the Helper Sheet is not required since we can identify the blank column headers in VBA code itself

Please Login or Register  to view this content.

[:) Sixthsense :)]

VBA macros will not run when formulas are recalculated

Just wondering whether this can able to do the autorun rask

Please Login or Register  to view this content.

Please correct me if I am wrong

[Trebor76]

As well as Sixthsense's clever solution, have a look at berlan's and my solution here where though the macros suggested hide columns, they could be easily modified to delete them.

[Reapz]

Thanks guys. Sorry for the late reply!

I tried it on the worksheet I uploaded and it works awesomely, but I'm now trying it in the actual spreadsheet I made up (a LOT bigger) and I just can't it working. I'll persevere with it this afternoon/tomorrow and get back to you. Appreciate the responses, I'm amazed by your guys knowledge.

[Reapz]

Hey guys, sorry to bring this back from the dead, SixthSense has been extremely helpful in helping me with this but I feel like an attached version would help speed the process up/lower the confusion.

The attached file has 3 tabs.
- The first tab "FullSchedule" is the one with all the data in it that needs to be formatted so the rest of my code will work (in another file unrelated).
- The second tab has the raw data that can be copy/pasted into 'FullSchedule' if the code fails.
- The third tab is what the data should look like if the macro works perfectly.

Due to the SAP exports leaving random columns empty, sometimes it has two blank columns next to each other (in this case, columns O & P).

The macro's I have from SixthSense's help are:

1. "ThisKindOfWorks" macro does exactly what I need it to do, but does not delete column P (which becomes column L once every other blank column from row 9 (except for column C) is deleted.
2. "DeleteBlankColumnHeaderColumns" - Is the code that goes from right to left, but it seems to delete almost everything.

Sorry for the essay, appreciate any help available. Cheers. +rep for any assistance!

Edit: Just quickly - some clarification. When the SAP export has 1 empty column, then a full column etc etc, the macro 'ThisKindOfWorks' actually works really well. It's only when there's two empty columns next to each other that the code doesn't work fully..

Edit #2 - I've figured out how to get it to work by running the macro twice. I'll try work out how to get it to work by using this method unless someone has any better ideas. Thanks again for any help.

Edit #3 - Totally figured it out and implemented it into my project. Would like to publicly thank SixthSense for all of his help. Solved.