Column Delete if Header contains XXX

[Erichev]

I have a .CSV that I pull from SCCM. I convert it to .XLSX and then begin modifying it. I try to create my own macros when I can, but I seem to be stuck on something that I hope should be relatively simple.

The report has a random number of columns and rows based on the required output. I need to delete all of the columns that contain "Header_Table0_" in Row 1. I can select them and delete them all, but I want automation. I have already done some recording (easiest method for n00bs) for modifying the other portions of the spreadsheet. I have the macro Finding and Removing "Details_Table0_", resizing the cells, adding filters, and sorting based on ComputerName (originally had Details_Table0_ leading) column. I can put all of this at the end of the Header removal macro to perform the entire operation. Unless these steps are all simple too.

Header_Table0_ComputerName	Header_Table0_ComputerManu	Header_Table0_ComputerModel	Details_Table0_ComputerName	Details_Table0_ComputerModel
Computer Name	Computer Manufacturer	Computer Model	CCCC2332	20B7
Computer Name	Computer Manufacturer	Computer Model	CCCD3323	20UC

<tbody>
</tbody>

Of course, as mentioned, this table can vary in columns it contains and the information in each column. So I can't just look for "Header_Table0_*", "Computer Name", et.al. without having the macro fail at some point (usually the column isn't there that time). Plus I still need to remove the leading now empty columns (which the number can vary). With my recording technique I can't allow for variables.

I will be using this to learn from. So, if side notes can be added to show me what each step is accomplishing that would be wonderful. VBA is much harder to understand than I thought and I've read that using recording can write the worst kinds of macros.

Thanks in advance.

 

[Joe4]

Try this:

Sub MyDeleteColumns()

    Dim lastCol As Long
    Dim col As Long
    
'   Find last column with data in row 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns (going backwards)
    For col = lastCol To 1 Step -1
'       Delete columns with specific start of header
        If Left(Cells(1, col), 13) = "Header_Table0" Then
            Cells(1, col).EntireColumn.Delete
        End If
    Next col
        
End Sub

 

[Erichev]

Try this:

Sub MyDeleteColumns()

    Dim lastCol As Long
    Dim col As Long
    
'   Find last column with data in row 1
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns (going backwards)
    For col = lastCol To 1 Step -1
'       Delete columns with specific start of header
        If Left(Cells(1, col), 13) = "Header_Table0" Then
            Cells(1, col).EntireColumn.Delete
        End If
    Next col
        
End Sub

Thank you very much!!

 

[Joe4]

You are welcome.