Solved

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

Posted on 2011-03-13
6
201 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 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

724 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