CODEWITHSUNDEEP
excelvbaexcel-formula
thomasuponor
thomasuponor

Reputation: 23

VBA hide rows with multiple conditions

I need help to hide rows if (will use rows 148-149 as example):

If Either J OR P 148 have a value >0 and both J AND P 149 =0 THEN 149 should hide.

IF either J OR P 149 >0 THEN BOTH 148 AND 149 should show

IF value >0 in J OR P 148 AND J OR P 149 >0 then show 148 AND 149

Example

This should be the final result

    Sub Skjul_0_Storkundeaftale()
 Dim beginRow As Long, endRow As Long, CheckCol_1 As Long, rngHide As Range
  beginRow = 148 'first row'
  endRow = 176 'last row'
  CheckCol_1 = 10 'column number --> O'

For rowNum = beginRow To endRow
    If Cells(rowNum, CheckCol_1).Value <= 0 And Cells(rowNum + 1, CheckCol_1).Value = "" Then
        Cells(rowNum, CheckCol_1).EntireRow.Hidden = True
    End If
Next rowNum
End Sub

Upvotes: 0

Views: 638

Answers (3)

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please:

Sub Skjul_0_Storkundeaftale()
 Dim beginRow As Long, endRow As Long, rowNum As Long
  beginRow = 148 'first row'
  endRow = 176  'last row'

 For rowNum = beginRow To endRow
    If Cells(rowNum, "P").value <= 0 Or Cells(rowNum, "J").value <= 0 Then
        If Len(Cells(rowNum, "P").value) <> 0 Then
            If Len(Cells(rowNum + 1, "P").value) <> 0 Then
                If Cells(rowNum + 1, "P").value = 0 And _
                    Cells(rowNum + 1, "J").value = 0 And _
                        Cells(rowNum + 2, "J").value = Empty Then
                    Cells(rowNum + 1, "P").EntireRow.Hidden = True
                    rowNum = rowNum + 1
                End If
            End If
        End If
    End If
 Next rowNum
End Sub

Upvotes: 0

Wesley
Wesley

Reputation: 300

Here is a new code. Maybe not the best coding but it does the trick :)

Sub Skjul_0_Storkundeaftale()

beginRow = 148 'first row'
endRow = 176 '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 + 1, 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

Upvotes: 1

Wesley
Wesley

Reputation: 300

Sub Skjul_0_Storkundeaftale()

beginRow = 148 'first row'
endRow = 176'last row'
CheckCol_1 = 10 'column number --> O'
CheckCol_2 = 2 ' column b

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

       End If
    Next rowNum

End Sub

You can use something like this. You can still navigate row numbers within the brackets. So if you want to check 1 row below, you just doe rowNum + 1. You can also add other 'AND' of 'IF' rules to your code.

Upvotes: 0

Related Questions