Hiding Columns Based on Criteria

Carpiquty

New Member
Joined
Apr 7, 2022
Messages
2
Office Version
  1. 365
Hello, Everyone,

I am struggling to get a macro working. I'm trying to hide columns if the entry in a particular row doesn't match the value in a specific cell. I'm really just trying to create a filter for columns in order to make using a large excel sheet easier.

I have the below:
Sub HideCols()
Dim cell As Range
For Each cell In Range("Resource")
If Range("ResourceFilter") = "All" Then
cell.EntireColumn.Hidden = False
ElseIf cell.Value = Range("ResourceFilter").Value Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

The named ranges "Resource" and "ResourceFilter" seem to be correctly listed in the Name manager with the Scope "Workbook". When I press the button to run the macro, the module window with the code pops up with the line I have bolded here highlighted in yellow. It doesn't seem to give me an error code but the macro does not seem to be hiding any columns even when the values in the cells in the Range "Resource" don't match the value in the "ResourceFilter" cell.

Any ideas?

Thanks in advance!
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
I got fed up and copy and pasted the exact code to a new module and for some reason it's working without issue now. Frustrating but glad it's over.
 
Upvote 0
Solution

Forum statistics

Threads
1,223,894
Messages
6,175,250
Members
452,623
Latest member
Techenthusiast

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
Back
Top