Hide Rows Depending on Cell Value (Text)

[ldngo]

I am writing up a Macro to be used in my worksheet and I was hoping that you could please help me figure out why it’s not working exactly how I intended.

I have a worksheet that pulls information from several other worksheets to provide a score and any discrepancies observed. I am trying to make all rows that have a “N/A” (text) listed in the column D to automatically hide when the Macro runs.

Here is what I have so far:

Sub HideRows()
On Error Resume Next
With Range("A9:D142")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = "N/A" Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
End Sub

Instead of hiding only the rows with “N/A” in Column D, it hides all the rows.

Can you please let me know what I am doing wrong?

Thank you for your help in advance!

 

[adam087]

Perhaps

If .Cells(i, 4).value = "N/A" Then

Does that help?

/AJ

 

[ldngo]

Good Afternoon Adam!

This works out great!!! Thank you so much for your help!

Do you know if there is a way to also incorporate code that will hide cells that aren't filled out (or currently show up at "0" because of the formulas I currently have in the cells)?

Thank you again for your help in advance!

 

[adam087]

Well you can't 'hide' single cells because if you hide them then you'll lose the entire row.
Do you mean you just want them to show as empty rather than as zero?

You can do that by setting the number format to 'general;-general;' (note the trailing semi-colon with nothing after it). You can adapt that general to be whatever format you want.
That could either be done by code or just manually set the entire region.

/AJ

 

[ldngo]

I was actually wondering if you could please help me hide rows that show up as zero as well as the rows that show the "N/A". Do I just put the same code after the first "End With"?

Thank you!

 

[adam087]

What you say would work, so nothing wrong with it. But we could be a little tidier and put an OR clause in to the first If statement to check for either condition.

If .Cells(i, 4).Value = "N/A" Or .Cells(i, 4).Value = 0 Then

Hope that helps.

/AJ

 

[Michael M]

Maybe this way...

Sub MM1()
    With ActiveSheet.UsedRange
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="<>#N/A", Operator:=xlAnd, Criteria2:="<>0"
End With
End Sub

 

[adam087]

Maybe this way...

Sub MM1()
    With ActiveSheet.UsedRange
    .AutoFilter
    .AutoFilter Field:=4, Criteria1:="<>#N/A", Operator:=xlAnd, Criteria2:="<>0"
End With
End Sub

Much slicker. Respect. Thank you.

/AJ

 

[Michael M]

Thanks Adam...appreciate the comment...