Macro to Delete Columns where Header <> User Entry

[acemanhattan]

Hi everyone. I've searched for a solution to this, but I haven't found one that fit my exact needs and my skills aren't flexible enough to modify ones I've found into something that works.

I have two sheets "Inputs" and "Fees".

Inputs takes as an input two user chosen elements from the set {2013,2014,...2017} in cells C6 and C7.

Fees has the same values in the column headings from D9:H9.

What I'd like to have happen is that any columns not containing a value from either C6 or C7 on the Inputs page should be deleted.

Thanks in advance.

 

[JustynaMK]

Hi acemanhattan,

Please check this code that will hide all columns ("Fees" sheet) that do not match the dates specified in cells C6 and C7, "Inputs" tab.

Remove the first highlighted line:
Sheets("Fees").Range("D:H").EntireColumn.Hidden = False
...and modify the second line:
Columns(i).EntireColumn.Delete
...if you want to permanently remove the columns.

I hope it helps. Good luck!

Sub fees()
Dim i, i1, i2 As Integer


i1 = Sheets("Inputs").Range("C6").Value
i2 = Sheets("Inputs").Range("C7").Value


Sheets("Fees").Select
[B]Sheets("Fees").Range("D:H").EntireColumn.Hidden = False[/B]


For i = 4 To 8
    If Sheets("Fees").Cells(9, i).Value <> i1 _
    And Sheets("Fees").Cells(9, i).Value <> i2 Then
[B]        Columns(i).EntireColumn.Hidden = True[/B]
    End If
Next i


End Sub

 

[acemanhattan]

Hi acemanhattan,

Please check this code that will hide all columns ("Fees" sheet) that do not match the dates specified in cells C6 and C7, "Inputs" tab.

Remove the first highlighted line:
Sheets("Fees").Range("D:H").EntireColumn.Hidden = False
...and modify the second line:
Columns(i).EntireColumn.Delete
...if you want to permanently remove the columns.

I hope it helps. Good luck!

Sub fees()
Dim i, i1, i2 As Integer


i1 = Sheets("Inputs").Range("C6").Value
i2 = Sheets("Inputs").Range("C7").Value


Sheets("Fees").Select
[B]Sheets("Fees").Range("D:H").EntireColumn.Hidden = False[/B]


For i = 4 To 8
    If Sheets("Fees").Cells(9, i).Value <> i1 _
    And Sheets("Fees").Cells(9, i).Value <> i2 Then
[B]        Columns(i).EntireColumn.Hidden = True[/B]
    End If
Next i


End Sub

Hello JustynaMK.

Thanks for the help, but so far I can't get this solution to work, but it might be because my setup is slightly different than I described (note that I've tried it in the original format and it didn't work correctly there either).

My actual Inputs tab has user entry in cells E6 and E7 and my column headers are now on a "Model" tab with the headings in AD49:AR49. The coluimn headings are 2017,2017,2017,2106,2016,2016,...2013,2013,2013 (I.E. each year repeats three times).


I am using the following code:

Sub Fees()Dim i, i1, i2 As Integer

i1 = Sheets("Inputs").Range("E6").Value
i2 = Sheets("Inputs").Range("E7").Value

Sheets("Model").Select

For i = 30 To 44
    If Sheets("Model").Cells(49, i).Value <> i1 _
    And Sheets("Model").Cells(49, i).Value <> i2 Then
        Columns(i).EntireColumn.Delete
    End If
Next i
End Sub

If I enter 2016 in E6 and 2017 in E7 the Macro will successfully skip 2017,2017,2017,2016,2016,2016, but it will only delete 2 of 3 columns for each 2015,2014,2103.

 

[JustynaMK]

Ahh ok, I see your point. Please try the following:

Sub Fees()
Dim i, i1, i2 As Integer


i1 = Sheets("Inputs").Range("E6").Value
i2 = Sheets("Inputs").Range("E7").Value


Sheets("Model").Select


For i = 44 To 30 Step -1
    If Sheets("Model").Cells(49, i).Value <> i1 _
    And Sheets("Model").Cells(49, i).Value <> i2 Then
        Columns(i).EntireColumn.Delete
    End If
Next i


End Sub

 

[acemanhattan]

Ahh ok, I see your point. Please try the following:

Sub Fees()
Dim i, i1, i2 As Integer


i1 = Sheets("Inputs").Range("E6").Value
i2 = Sheets("Inputs").Range("E7").Value


Sheets("Model").Select


For i = 44 To 30 Step -1
    If Sheets("Model").Cells(49, i).Value <> i1 _
    And Sheets("Model").Cells(49, i).Value <> i2 Then
        Columns(i).EntireColumn.Delete
    End If
Next i


End Sub

Thanks for the solution, I will try it later today. Can you tell me what the addition of "Step -1" does in the adjustment you made?

 

[JustynaMK]

Previously the code was set up to check columns 30 to 44 and hide the ones with invalid headers. Unfortunately it fails when you want to remove the columns (instead of hiding them), because every time a column is being removed its column number shifts back to the previous number -> for example, column 35 is being removed, so column 36 becomes the "new" 35, but macro skips it and checks column no. 36. That is why some columns were not being properly removed by the code.
The new macro is now checking columns "backwards" (44 to 30, thanks to Step -1), so it works perfectly fine with columns being removed.
Apologies for a messy description, I hope it is clear...

 

[acemanhattan]

Previously the code was set up to check columns 30 to 44 and hide the ones with invalid headers. Unfortunately it fails when you want to remove the columns (instead of hiding them), because every time a column is being removed its column number shifts back to the previous number -> for example, column 35 is being removed, so column 36 becomes the "new" 35, but macro skips it and checks column no. 36. That is why some columns were not being properly removed by the code.
The new macro is now checking columns "backwards" (44 to 30, thanks to Step -1), so it works perfectly fine with columns being removed.
Apologies for a messy description, I hope it is clear...

Thanks for the clear explanation! I had a feeling that was what was going wrong with the original Macro, but wasn't sure how to fix it.

Still haven't had a chance to implement the new macro, but I'll let you know how it goes.

 

[JustynaMK]

OK - fingers crossed!

 

[acemanhattan]

JustynaMK, this worked great! Thanks again.

 

[JustynaMK]

No problem at all, take care!