Home

Hiding columns using VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-288153%22%20slang%3D%22en-US%22%3EHiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288153%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20spreadsheet%20where%20the%20balance%20sheet%20and%20income%20summaries%20for%20each%20month%20of%202017%20and%202018%20are%20presented.%26nbsp%3B%20To%20clarify%2C%26nbsp%3BJanuary%202017%20is%20next%20to%26nbsp%3BJanuary%202018%2C%20February%202017%20is%20next%20to%20February%202018%2C%20etc.%3C%2FP%3E%3CP%3EI'm%20trying%20to%20write%20a%20Macro%20in%20VBA%20that%20hides%20all%20of%20the%202017%20columns%20or%20the%202018%20columns%20depending%20upon%20the%20year%20selected%20from%20a%20drop%20down%20box.%26nbsp%3B%20So%20far%2C%20I%20can%20hide%202017%2C%20but%20then%20if%20I%20pick%202018%2C%20everything%20is%20hidden.%26nbsp%3B%20I%20need%20for%20the%20routine%20to%20hide%20one%20year%20and%20not%20hide%20the%20other.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20what%20I%20have%20done%20so%20far%3A%3C%2FP%3E%3CDIV%3ESub%20Hidecolumnsbasedoncellvalue()%3C%2FDIV%3E%3CDIV%3EDim%20p%20As%20Range%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20For%20Each%20p%20In%20Range(%22D4%3AAP4%22).Cells%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20If%20p.Value%20%3D%20Range(%22C2%22).Cells%20Then%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20p.EntireColumn.Hidden%20%3D%20True%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20End%20If%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Next%20p%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%3CBR%20%2F%3EEnd%20Sub%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3EAny%20ideas%20of%20how%20to%20fix%20it%3F%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-288153%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288366%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288366%22%20slang%3D%22en-US%22%3Eglad%20to%20hear%20it%20helped!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288214%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288214%22%20slang%3D%22en-US%22%3EVery%20simple%20fix%20and%20it%20works!%20Thank%20you%20very%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288213%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288213%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20works!%26nbsp%3B%20Much%20more%20sophisticated%20than%20mine%20and%20I%20don't%20understand%20it%20all%20yet%2C%20but%20thank%20you%20very%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288165%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288165%22%20slang%3D%22en-US%22%3E%3CP%3EJust%20add%20this%20line%20before%20for-loop%3C%2FP%3E%3CPRE%3EColumns(%22D%3AAP%22).EntireColumn.Hidden%20%3D%20False%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288164%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288164%22%20slang%3D%22en-US%22%3E%3CP%3Epls%20see%20attached%20sample.%3C%2FP%3E%3CP%3Ehope%20you%20can%20work%20something%20out%20of%20it.%3C%2FP%3E%3CP%3Ethanks..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288157%22%20slang%3D%22en-US%22%3ERe%3A%20Hiding%20columns%20using%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288157%22%20slang%3D%22en-US%22%3E%3CP%3Ebefore%20hiding%202018%20-%20unhide%202017%20first.%20and%20vice-versa%3CBR%20%2F%3Ewouldn't%20that%20work%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ronald Seabaugh
New Contributor

I have a spreadsheet where the balance sheet and income summaries for each month of 2017 and 2018 are presented.  To clarify, January 2017 is next to January 2018, February 2017 is next to February 2018, etc.

I'm trying to write a Macro in VBA that hides all of the 2017 columns or the 2018 columns depending upon the year selected from a drop down box.  So far, I can hide 2017, but then if I pick 2018, everything is hidden.  I need for the routine to hide one year and not hide the other.

 

Here's what I have done so far:

Sub Hidecolumnsbasedoncellvalue()
Dim p As Range
        For Each p In Range("D4:AP4").Cells
            If p.Value = Range("C2").Cells Then
            p.EntireColumn.Hidden = True
        End If
                  
    Next p
        
End Sub
 
Any ideas of how to fix it?

 

6 Replies

before hiding 2018 - unhide 2017 first. and vice-versa
wouldn't that work?

pls see attached sample.

hope you can work something out of it.

thanks..

 

Just add this line before for-loop

Columns("D:AP").EntireColumn.Hidden = False

 

It works!  Much more sophisticated than mine and I don't understand it all yet, but thank you very much!

Very simple fix and it works! Thank you very much!
glad to hear it helped!
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies