?
Solved

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

Posted on 2011-03-13
6
Medium Priority
?
203 Views
Last Modified: 2012-05-11
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
Comment
Question by:easycapital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 35123420
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
 
LVL 4

Expert Comment

by:SmittyPro
ID: 35130585
Why not just use AutoFilter:

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

HTH,
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35131015
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:SmittyPro
ID: 35131608
Guess we'll just have to wait and see. :)
0
 

Author Comment

by:easycapital
ID: 35167919
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
 

Author Closing Comment

by:easycapital
ID: 35178962
Thanks,
JP
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question