Macro to hide entire row if value from 2 columns are blank

[BenR]

Hello,
I require a macro to hide entire rows based on the criteria of having blank values in BOTH columns C & D.

Thanks in advance

BENR

 

[Jon von der Heyden]

Hello BENR

This is how you would do it manually:

Highlight columns C:D
Go to the data tab > filter
In the filter drop down for each filter, scroll to the end of the list and uncheck 'Blanks'

You should be able to follow these steps with macro recorder to get the VBA code.

 

[Michael M]

try

Sub MM1()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    If Range("C" & r).Value = "" And Range("D" & r).Value = "" Then Rows(r).Hidden = True
Next r
End Sub

 

[Jon von der Heyden]

One hit:

Public Sub HideBlankRows()
    Dim rngToCheck As Excel.Range


    With Excel.ActiveSheet
        Set rngToCheck = Excel.Intersect(.Columns("C:D"), .UsedRange)
    End With


    With rngToCheck
        Call .AutoFilter(Field:=1, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=False)
        Call .AutoFilter(Field:=2, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=False)
    End With
End Sub

 

[BenR]

Can we also try similar but EITHER blank values in columns C or D hide row
Sorry - I shauld have asked for this option aswell first time around as well..
I require EITHER blank values in columns C or D to hide row.
Thanks inadvance.

 

[Michael M]

Just change the AND to OR

Sub MM1()
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
    If Range("C" & r).Value = "" OR Range("D" & r).Value = "" Then Rows(r).Hidden = True
Next r
End Sub

 

[dermie_72]

you could modify Michael M's macro :
If Range("C" & r).Value = "" And Range("D" & r).Value = "" Then Rows(r).Hidden = True
to
If Range("C" & r).Value = "" OR Range("D" & r).Value = "" Then Rows(r).Hidden = True

Or you could have 3 of Jon con der Heyden's macro's,

Hideblankrowsc()
change: Call .AutoFilter(Field:=2, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=False)
to: Call .AutoFilter(Field:=2, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=True)

Hideblankrowsd()
change: Call .AutoFilter(Field:=1, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=False)
to: Call .AutoFilter(Field:=1, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=True)



Or....you could add column E add the following formula - =IF(AND(C2="",D2=""),"",IF(OR(C2="",D2=""),"","X")) 'or whatever value you want other than "X"
copy that down.

Expand Jon's tange to: Set rngToCheck = Excel.Intersect(.Columns("C:E"), .UsedRange)

and have only one call:
Call .AutoFilter(Field:=3, Criteria1:="<>", Operator:=xlAnd, VisibleDropDown:=False)

Multiple options there. I think they should all work

 

[BenR]

oh..! silly me.

 

[Michael M]

No, not silly at all.
All part of the learning curve....

 

[BenR]

Excellent - all methods work to perfection.
However, one more senario.
Filter Columns C & D to only show rows with either a value in C, or D, or both.
In advance..thanks