Hide rows if certain rows are blank

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Hello!

I hope to find you well

I would like to know if there is any code in VBA, that allows hide rows if they are empty. In my case, I would like to hide rows from 40 to 327, if the rows are empty (or if column A of that row is empty).


Also, I already have this code in the sheet I am talking about:

SQL:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D10,H20")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="123"
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        Select Case Target.Address
            Case "$D$10"
                Select Case Target.Value
                    Case Is = 1, 2
                        .CheckBoxes("Check Box 7").Visible = False
                        .CheckBoxes("Check Box 8").Visible = False
                        .Rows("33:34").EntireRow.Hidden = False
                    Case Is = 3, 4
                        .CheckBoxes("Check Box 9").Visible = True
                        .CheckBoxes("Check Box 10").Visible = True
                        .Rows("33:34").EntireRow.Hidden = True
                        .Rows("31:32").EntireRow.Hidden = False
                End Select
            Case "$H$20"
                Select Case Target.Value
                    Case "PRODUCT A"
                        .CheckBoxes("Check Box 5").Visible = False
                        .CheckBoxes("Check Box 6").Visible = False
                        .CheckBoxes("Check Box 1").Visible = True
                        .CheckBoxes("Check Box 2").Visible = True
                        .Rows("21:22").EntireRow.Hidden = True
                    Case "PRODUCT B"
                        .CheckBoxes("Check Box 5").Visible = True
                        .CheckBoxes("Check Box 6").Visible = True
                        .CheckBoxes("Check Box 1").Visible = True
                        .CheckBoxes("Check Box 2").Visible = True
                        .Rows("21:22").EntireRow.Hidden = False
                End Select
        End Select
    End With
    ActiveSheet.Protect Password:="123"
    Application.ScreenUpdating = True
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
Thank you @Logit, I was trying to do it without buttons, but this will do the work.
Thank you very much!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,958
You can place a call to the HIDE macro, within the Sheet Change event you already have.

Google "VBA Call a macro".
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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