+ Reply to Thread
Results 1 to 2 of 2

If cells are empty hide rows

Hybrid View

  1. #1
    Registered User
    Join Date
    05-25-2020
    Location
    Denmark
    MS-Off Ver
    Professional Plus 2016
    Posts
    58

    If cells are empty hide rows

    Hi,

    I have added a few new rows and now I can't get it to work.

    These are the conditions:

    If all cells with the 6 X are empty = hide row 150-152
    If J151 or P151 have any value = keep row and also keep row 150
    If J152 or P152 have any value = keep row and also keep row 150
    If only row 150 has values = hide row 151 and 152

    Capture.PNG

    Formula: copy to clipboard


    Sub Skjul_0_Projektaftale()

    beginRow = 150 'first row'
    endRow = 180 'last row'
    CheckCol_1 = 4 'column number --> Gulvarme
    CheckCol_2 = 10 'First column
    CheckCol_3 = 16 'Second column

    For rowNum = beginRow To endRow
    If Cells(rowNum, CheckCol_1).Value <> "" And Cells(rowNum + 1, CheckCol_2).Value = 0 And Cells(rowNum + 1, CheckCol_3).Value = 0 Then
    Cells(rowNum + 2, CheckCol_1).EntireRow.Hidden = True

    End If
    Next rowNum

    For rowNum = beginRow To endRow
    If Cells(rowNum, CheckCol_1).Value <> "" And Cells(rowNum, CheckCol_2).Value = 0 And Cells(rowNum, CheckCol_3).Value = 0 _
    And Cells(rowNum + 1, CheckCol_2).Value = 0 And Cells(rowNum + 1, CheckCol_3).Value = 0 Then
    Cells(rowNum, CheckCol_1).EntireRow.Hidden = True

    End If
    Next rowNum


    End Sub


  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: If cells are empty hide rows

    Try this amended code:
    Sub Skjul_0_Projektaftale()
    
        Dim rowNum As Long, beginRow As Long, endRow As Long
        Dim CheckCol_1 As Long, CheckCol_2 As Long, CheckCol_3 As Long
    
        beginRow = 150 'first row'
        
        endRow = 180 'last row'
        
        CheckCol_1 = 4 'column number --> Gulvarme
        
        CheckCol_2 = 10 'First column
        
        CheckCol_3 = 16 'Second column
        
        For rowNum = beginRow To endRow
            If Cells(rowNum, CheckCol_1).Value <> "" Then
                Rows(rowNum).Resize(3).Hidden = True 'hide all 3 rows first
                
                If Cells(rowNum, CheckCol_2).Value <> "" Or Cells(rowNum, CheckCol_3).Value <> "" Then Rows(rowNum).Hidden = False 'first row has values, unhide
                
                If Cells(rowNum + 1, CheckCol_2).Value <> "" Or Cells(rowNum + 1, CheckCol_3).Value <> "" Then
                    Rows(rowNum).Resize(2).Hidden = False 'second row has values, unhide first and second rows
                End If
                
                If Cells(rowNum + 2, CheckCol_2).Value <> "" Or Cells(rowNum + 2, CheckCol_3).Value <> "" Then
                    Rows(rowNum).Hidden = False 'third row has values, unhide first and third rows
                    Rows(rowNum + 2).Hidden = False
                End If
            End If
        Next rowNum
        
    End Sub
    多么想要告诉你 我好喜欢你

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hide rows based on empty cells in other sheet
    By HereComesTheBoom in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-24-2018, 09:46 AM
  2. [SOLVED] Auto hide/unhide rows that have empty cells ( Preferably VBA Code)
    By carlandtina02 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-09-2016, 07:06 PM
  3. [SOLVED] Hide / Unhide rows in a table that the first column cells are empty
    By How How in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2014, 05:46 AM
  4. Hide worksheets and rows based on empty cells
    By bgasypher in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-03-2014, 03:23 PM
  5. Replies: 0
    Last Post: 01-04-2012, 10:34 AM
  6. Excel 2007 : How to Hide rows if data cells are empty?
    By gene070752 in forum Excel General
    Replies: 0
    Last Post: 12-04-2011, 05:00 PM
  7. Replies: 0
    Last Post: 09-07-2011, 07:51 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1