Macro to Delete Columns where Header <> User Entry

acemanhattan

New Member
Joined
May 11, 2016
Messages
45
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

JustynaMK

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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!

Code:
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

New Member
Joined
May 11, 2016
Messages
45
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!

Code:
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:

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

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
Ahh ok, I see your point. Please try 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 = 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

New Member
Joined
May 11, 2016
Messages
45

ADVERTISEMENT

Ahh ok, I see your point. Please try 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 = 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

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
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

New Member
Joined
May 11, 2016
Messages
45

ADVERTISEMENT

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

Well-known Member
Joined
Aug 28, 2016
Messages
674
Office Version
  1. 365
  2. 2013
Platform
  1. Windows
No problem at all, take care!
 

Forum statistics

Threads
1,136,258
Messages
5,674,669
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top