Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Trying to hide rows in Excel

    Hello, This is my first time posting here and my first time using code in excel. I have basic coding experience in python. I am trying to hide rows depending on its corresponding cell value. So far I have managed to hide individual row using this code inserted into visual basic sheet3.
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
         BeginRow = 7    
         EndRow = 34
         ChkCol = 1
    
         For RowCnt = BeginRow To EndRow  
             If Range("A8").Value = 3 Then  
               Rows(7).EntireRow.Hidden = True 
             Else   
               Rows(7).EntireRow.Hidden = False
            End If 
        Next RowCnt 
    
    End Sub
    I then adapted this to the below. It does not work, I suspect it is something to do with the first line.

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    
        BeginRow = 7 
        EndRow = 34 
        ChkCol = 3 
    
        For RowCnt = BeginRow To EndRow
            If Cells(RowCnt, ChkCol).Value = 3 Then  
              Rows(7).EntireRow.Hidden = True
            Else   
               Rows(7).EntireRow.Hidden = False  
            End If     
        Next RowCnt
    End Sub
    Ideally, I would like to toggle this code using a button. Thanks
    Last edited by RetiredGeek; 2015-03-16 at 08:08. Reason: Added Code Tags

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Donut,

    Welcome to the Lounge as a new poster!

    I believe what you want is:
    Code:
            If Cells(RowCnt, ChkCol).Value = 3 Then
              Rows(RowCnt).EntireRow.Hidden = True
            Else
               Rows(RowCnt).EntireRow.Hidden = False
            End If
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Do you want to hide ONE row or many with the toggle? And, if you hide the same row as the cell used to hide the row with the toggle then how could you select to unhide if it is hidden?
    Perhaps you mean that if a cell on a different row??? Pls explain fully such as "when I select a3 if it is 3 hide row 7, if not unhide row 7. Below does that
    '=============
    Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address <> Range("a3").Address Then Exit Sub
    If Target = 3 Then
    Rows(7).Hidden = True
    Else
    Rows(7).Hidden = False
    End If
    End Sub
    '============
    Don Guillett
    Excel Developer
    dguillett @gmail.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •