Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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