Solved

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

Posted on 2011-03-13
6
182 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
  • 2
  • 2
  • 2
6 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 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 92

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now