Forum Discussion

Angie Swirski's avatar
Angie Swirski
Copper Contributor
Jul 02, 2018

Turning Colums on and off to comare

Hello

I have been scouring all over to figure this out - I believe Excel can do it but just not sure how. I've tried a few things and nothing has worked. Following is a link to exactly I'd like to create to share internally with our own products. Anyone have any idea how to do this? Just want to turn the columns on and off to compare 1-3 products at a time, not all of them.

 

Here is the link:

 

http://www.trimble.com/mappingGIS/media/product_comparison/Handheld%20Computers%20With%20GNSS.html 

 

Thank you in advance!

developer
excel
Formulas and Functions
office 365

6 Replies

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jul 03, 2018

    Can't get your link to work.  However if you want to be able to select a few cells using Ctrl and make only their rows or columns visible and hide all of the other data you could use something like this (See attached .xlsm file for reference) Please note I have attached the example for columns and rows:

     

    Sub CompareCol()

    Dim LastCol As Long
    Dim Rng As Range
    Dim cll As Range
    
    LastCol = Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
    
    Set Rng = Selection
    
    Range(Columns(3), Columns(LastCol)).EntireColumn.Hidden = True
    
    For Each cll In Rng
        cll.EntireColumn.Hidden = False
    Next

End Sub

Sub ReverseCompareCol()

Dim LastCol As Long

    LastCol = Cells.Find(What:="*", _
                            After:=sh.Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
                            
    Range(Columns(3), Columns(LastCol)).EntireColumn.Hidden = False

End Sub

Sub Compare()

    Dim LastRow As Long
    Dim Rng As Range
    Dim cll As Range
    
    LastRow = Cells.Find(What:="*", _
                            After:=Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
                            
    Set Rng = Selection
    
    Rows("2:" & LastRow).EntireRow.Hidden = True
    
    For Each cll In Rng
        cll.EntireRow.Hidden = False
    Next

End Sub

Sub ReverseCompare()

    Dim LastRow As Long

    LastRow = Cells.Find(What:="*", _
                            After:=Range("A1"), _
                            Lookat:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
                            
    Rows("2:" & LastRow).EntireRow.Hidden = False

End Sub

     

     

     

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jul 03, 2018

      Matt, it looks like this

      With checkboxes you hide/unhide table columns.

       

      • Matt Mickle's avatar
        Matt Mickle
        Bronze Contributor
        Jul 03, 2018

        Sergei-

         

        Thanks for relaying the screenshot.  

         

        Angie-

         

        I didn't immediately think of the easiest solution because I figured this was not what you were looking for... but you can also:

         

        1. Select an Entire Column or Row

        2. Right Click

        3. Hide / Unhide

         

        If the code is more preferable it should be easy to adjust if you wanted to only toggle certain columns or rows visibility on or off.  If you provide the specifics I would be happy to alter it accordingly.  It can also be modified to work like the website.   i.e. checking a box toggles a specific column on or off.

    • Angie Swirski's avatar
      Angie Swirski
      Copper Contributor
      Jul 03, 2018
      Thanks! I’ll play around with this.

      And it looks like the link works if you copy and paste it into your browser. Not sure why that is....
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 03, 2018

        Angie, I could be wrong but it looks like trimble.com uses outdated TSL security settings which not every browser supports now.

Resources