Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Macro show only columns where the cell in row A = 1 (excel 2003)

Or in other words, hide all others that do not contain a 1.

If looking into all the columns will cause a noticeable delay to the eye, then we could limit the columns of where the Macro looks for the values.  Perhaps it could be a range?  

Thanks,
Juan
0
easycapital
Asked:
easycapital
  • 2
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
Juan,

There is no "Row A".  If you meant "Row 1":

Sub HideCols()
    
    Dim LastC As Long
    Dim Counter As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Range(.Cells(1, LastC + 1), .Cells(1, .Columns.Count)).EntireColumn.Hidden = True
        For Counter = 1 To LastC
            
            'if you meant "hide where cell <> 1" then use:
            
            .Cells(1, Counter).EntireColumn.Hidden = (.Cells(1, Counter) <> 1)
            
            ' if you meant "hide where cell does not contain 1" then use:
            
            '.Cells(1, Counter).EntireColumn.Hidden = (Not .Cells(1, Counter) Like "*1*")
            
        Next
    End With
    
    Application.ScreenUpdating = True
    
    MsgBox "Done"
    
End Sub

Open in new window


Patrick
0
 
SmittyProCommented:
Why not just use AutoFilter:

ActiveSheet.Range("A:A").AutoFilter Field:=1, Criteria1:="1"

HTH,
0
 
Patrick MatthewsCommented:
Hi Smitty :)

If the Asker is trying to hide the values in a single column, then I agree with you on the AutoFilter.

I had a different reading of the question--which may or may not have been the right one :)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SmittyProCommented:
Guess we'll just have to wait and see. :)
0
 
easycapitalAuthor Commented:
Patrick,

You hitted right on the mark.  Could you adjust to equal "Show" instead of 1.

Also, could you include the code unhide all the columns :)

Thanks,
JP
0
 
easycapitalAuthor Commented:
Thanks,
JP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now