hide/unhide rows based on cell data in a specific column

Trent30

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
If I have a formula in column A that will have either "Hide" or "Show" depending on mulitple results in other cells what would the VBA be?

I have seen multiple versions based on hiding rows based on a specific cell in the spreadsheet but I cant find it based on each row
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,718
Try:

VBA Code:
Sub FilterRow()
Worksheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:="Hide"
End Sub
 

Trent30

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Try:

VBA Code:
Sub FilterRow()
Worksheets("Sheet1").Range("A1").AutoFilter Field:=1, Criteria1:="Hide"
End Sub
Sorry, I tried this and changed it to "Sheet2" saved, closed then re-opened and still nothing happened
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,718
Are the headers in row 1 and data in row 2 downwards?

Did it at least put filters on? If you click the dropdown on the filter does it look like it's working?
 

Trent30

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Below is what the sheet looks like at this stage, however column 5 has a drop down box that selects a number and what ever number is selected will then change the following cells in column A to show. I have tried to use a drop down box and the subsequent code, however I will also be adding further conditions to why it should hide a whole section and thought it would be easier to base it on the specific cell for the row.

Sorry new to VBA codes still trying to find my way around. When you say did it put the filters on are you meaning like the drop down buttons at the top of the column? as I want it to automatically hide the row

Hide/ShowNumberNameShow lines
4​
Show
1​
Ted
Show
2​
Tom
Show
3​
Taylor
Show
4​
Trevor
Hide
5​
Tabitha
Hide
6​
Tony
Hide
7​
Terry
Hide
8​
Tim
Hide/ShowNumberNameShow lines
6​
Show
9​
Carly
Show
10​
Cheryl
Show
11​
Charlotte
Show
12​
Charlie
Show
13​
Chris
Show
14​
Christine
Hide
15​
Christy
Hide
16​
Con
Hide/ShowNumberNameShow lines
2​
Show
9​
Carly
Show
10​
Cheryl
Hide
11​
Charlotte
Hide
12​
Charlie
Hide
13​
Chris
Hide
14​
Christine
Hide
15​
Christy
Hide
16​
Con
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,718
Thanks for the extra info:

Last code probably would have just hidden the ones in the top table (rows 1-9). Same as if you clicked on A1 and selected filter.

VBA Code:
Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next i

End Sub
 

Trent30

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are the headers in row 1 and data in row 2 downwards?

Did it at least put filters on? If you click the dropdown on the filter does it look like it's working?

Sorry not sure if you got the last message since I didn't reply to your post
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,718
I did, did the revised solution work?

I was initially assuming your data was in one table, not the multiple it was actually in.
 

Trent30

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Thanks for the extra info:

Last code probably would have just hidden the ones in the top table (rows 1-9). Same as if you clicked on A1 and selected filter.

VBA Code:
Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

  LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then
        Rows(i).Hidden = True
    Else
        Rows(i).Hidden = False
    End If
Next i

End Sub
Sorry do you need to put it in the workbook or the worksheet
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,962
Office Version
  1. 365
Platform
  1. Windows
@Trent30
Could you upload a sample workbook to a free site such as dropbox.com or google drive & then share the link here?
It will make it easier to test and find a solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,826
Messages
5,627,120
Members
416,223
Latest member
RichardHell

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