Deleting columns With Multiple Criteria excluding Header row

[deniskellyelphin]

Hi, I would like to delete all columns that only contain Blanks, or "n/a".

I want a code that deletes based on whether the column contains anything other than a combination of a specific word e.g. "n/a" and/or trimmed Blank cells e.g. "" and/or " ". If it contains something else e.g. other text such as "Yes" or a Number etc., I don't want to delete that Column! However, in assessing if to delete a column, I want it to ignore the header row!

The data is in range A:CS with Header in A1:CS1. The number of rows will vary from file to file but can go up to A8:CS3489, but most data sets will have much less, circa A8:CS200, so I would like to incorporate xEndCol and End(xlUp).Row commands (if that is the right way of saying it!)

I can find a code online to delete columns and with multiple criteria and code to delete columns based on sole criteria ignoring header rows, but
can't figure out how to merge the two so that it works... (I'm only starting my VBA journey!)

Any help really appreciated.

 

[Col Delane]

I would suggest the following modifications to your sheet layout as "helpers" rather than trying to do everything via VBA:

1. Insert several rows ABOVE the header row to give some space for the following.
2. Convert your data range to an Excel (Structured Reference) Table - this will make its dimensions expand automatically when new data is added beyond the existing table, and enable assessing the data easier.
3. Above each table header cell, enter a formula that tests whether the column contents matches your criteria for deletion of the column. By using the structured referencing to the table field names, you won't need to modify the range references as the table expands/contracts.
4. The formula would be something like this:
   • = IF( SUM( COUNTIFS( Table1[Column1], "n/a"), COUNTIFS( Table1[Column1], ""), COUNTIFS( Table1[Column1], " ") ) = ROWS( Table1[Column1] ), "DELETE", "Keep" )
   • The formula checks whether the count of all cells matching your delete criteria equals the number of rows in the table, in which case there are no cells containing something else (e.g. other text such as "Yes" or a Number etc.)
   • Modify the criterion in each Countif to suit.
   • Be careful when you copy the formula across columns as references to Table columns don't change relatively like those referencing a non-table does - but if you drag the black square in the bottom-right corner of the cell they will.
5. Your code could then assess the values in the above formula row and delete columns where this value is "DELETE". (Note that you could also use True/False in the formula and in the code in place of DELETE/Keep if desired) Also note that because this formula row is outside of the Table, your code doesn't need to use the Object Properties and Methods that apply to Tables (which are quite different to normal range objects)