Hide Rows Depending on Cell Value (Text)

ldngo

New Member
Joined
Jun 9, 2014
Messages
3
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!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Perhaps
Code:
If .Cells(i, 4).value = "N/A" Then

Does that help?

/AJ
 

ldngo

New Member
Joined
Jun 9, 2014
Messages
3
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-known Member
Joined
Jun 7, 2010
Messages
1,356
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

New Member
Joined
Jun 9, 2014
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
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.
Code:
If .Cells(i, 4).Value = "N/A" Or .Cells(i, 4).Value = 0 Then

Hope that helps.

/AJ
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,767
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

adam087

Well-known Member
Joined
Jun 7, 2010
Messages
1,356
Maybe this way...
Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,767
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Thanks Adam...appreciate the comment...(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,519
Messages
5,602,141
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top