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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

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, I am really a novice at this stuff so I don't completely understand how things work
VBA Code:
 is not part of the code?
I am  assuming that "A" & "1" are related to the column?
"i" stands for instance?
"sht" stands for Sheet?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,718
Hope this helps

VBA Code:
Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

''sht is a short name for the active sheet
Set sht = ActiveSheet

''this is finding the last row on the sheet in column A
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

''this is starting at i 1 and going up to the last row (i will basically represent the row number)
For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then ''cells(1,1) is cell A1, it will go to cells(2,1) next which is B1
        Rows(i).Hidden = True ''Hides the row it is up to
    Else
        Rows(i).Hidden = False ''if Hide is not found it will unhide (or not hide) the row
    End If
Next i ''changes the i variable to the next number

End Sub
 

Trent30

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

VBA Code:
Sub HideRow()

Dim sht As Worksheet
Dim LastRow As Long

''sht is a short name for the active sheet
Set sht = ActiveSheet

''this is finding the last row on the sheet in column A
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

''this is starting at i 1 and going up to the last row (i will basically represent the row number)
For i = 1 To LastRow
    If Cells(i, 1) = "Hide" Then ''cells(1,1) is cell A1, it will go to cells(2,1) next which is B1
        Rows(i).Hidden = True ''Hides the row it is up to
    Else
        Rows(i).Hidden = False ''if Hide is not found it will unhide (or not hide) the row
    End If
Next i ''changes the i variable to the next number

End Sub
Great I think I understand now, thank you for going through for me.

If I want it to automatically change to hide the cell rather than having to manually run the macro would this be different?
 

mrshl9898

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

ADVERTISEMENT

Add this to the worksheet module if you want it to run when a value changes in column A

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = 1 Then
    Application.Run ("HideRow")
    End If

End Sub
 

Trent30

New Member
Joined
Jan 12, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Add this to the worksheet module if you want it to run when a value changes in column A

VBA Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Column = 1 Then
    Application.Run ("HideRow")
    End If

End Sub
So does that mean the code will look like this?

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

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)



If Target.Column = 1 Then

Application.Run ("HideRow")

End If



End Sub
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,718
That should work, just make sure it's in the sheet module.

1610572976701.png
 

Watch MrExcel Video

Forum statistics

Threads
1,127,832
Messages
5,627,150
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