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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,958
.
Here are two macros ... one to hide and the other to unhide :

VBA Code:
Option Explicit

Sub HURows()
Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer

    BeginRow = 40
    EndRow = 327
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value <= 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub

Sub UnHRows()
Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer

    BeginRow = 40
    EndRow = 327
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value <= 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
.
Here are two macros ... one to hide and the other to unhide :

VBA Code:
Option Explicit

Sub HURows()
Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer

    BeginRow = 40
    EndRow = 327
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value <= 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
End Sub

Sub UnHRows()
Dim BeginRow As Integer
Dim EndRow As Integer
Dim ChkCol As Integer
Dim RowCnt As Integer

    BeginRow = 40
    EndRow = 327
    ChkCol = 1

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value <= 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        Else
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub
Hello! Thank you for your answer!
However, it is not working. I have tried to use that code after the one I already had (in the same sheet)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,958
Post your workbook to a download site and provide the link.
 

caet_

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

ADVERTISEMENT

Hello!

Here it is: https://easyupload.io/e8ohlb

I've slightly changed the code. Also, the empty rows between rows from 40 to 327 will depend of the cells D5 and H5 of sheet1.

Thank you for your help!
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,958
What is the password to unprotect Sheet 1 ?
 

caet_

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

ADVERTISEMENT

What is the password to unprotect Sheet 1 ?
Sorry, my bad. The password is 123
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,958
.
VBA Code:
Option Explicit
Sub HideRows()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Sheet1.Range("A40:A327")
    If c.Value = 0 Or c.Value = "" Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End Sub

Sub UnHideRows()
Application.ScreenUpdating = False
Rows("40:327").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
 

caet_

New Member
Joined
Nov 22, 2020
Messages
28
Office Version
  1. 2016
Platform
  1. Windows
.
VBA Code:
Option Explicit
Sub HideRows()
Dim c As Range
Application.ScreenUpdating = False
For Each c In Sheet1.Range("A40:A327")
    If c.Value = 0 Or c.Value = "" Then
        c.EntireRow.Hidden = True
    Else
        c.EntireRow.Hidden = False
    End If
Next c
Application.ScreenUpdating = True
End Sub

Sub UnHideRows()
Application.ScreenUpdating = False
Rows("40:327").EntireRow.Hidden = False
Application.ScreenUpdating = True
End Sub
Thank you.
I have tried to use it in the module and in the sheet1 and it does not work :(
 

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