Hiding Multiple Rows with one Command - VBA

Question

I am trying to hide multiple rows in an excel worksheet which are empty using following code however i am getting error message "Argument not optional". What could be wrong in the code?

Sub Attendance_Manday()
Dim sht1 As Worksheet
Dim row_count, col_count As Integer
Dim mainrange As Range
Dim startcell As Range
Set startcell = Range("B1")
Set sht1 = Sheets("Mandays")
row_count = Sheets("Mandays").Cells(Rows.Count, startcell.Column).End(xlUp).Row
col_count = Sheets("Mandays").Cells(startcell.Row, Columns.Count).End(xlToLeft).Offset(1, -2).Column
Set mainrange = sht1.Range(startcell.Address & ":" & sht1.Cells(row_count, col_count).Address)
mainrange.Range.SpecialCells(xlCellTypeBlanks).Rows.Hidden = True
End Sub

Maybe its because there are cells which are empty but you cant just hide one cell, you need to hide the row or column. mainrange.Range.SpecialCells(xlCellTypeBlanks).Rows.Hidden = True — snenson, Apr 21 '21 at 06:02
Used the Rows argument as suggested but getting same error again. — KhawarAmeerMalik, Apr 21 '21 at 06:07
however when I use this code `mainrange.SpecialCells(xlCellTypeBlanks).Rows.Hidden = True` it makes all rows hidden regardless if they are empty or not. — KhawarAmeerMalik, Apr 21 '21 at 06:09
As far as i see it, its not that easy with xlCellTypeBlanks because it selects all the blank cells. So if there is any blank cell in the row it hides it. You could make a loop which looks through all rows. — snenson, Apr 21 '21 at 06:53
Actually loop was making the process very slow. I wanted a fast option. Any way thank you for your response @snenson — KhawarAmeerMalik, Apr 21 '21 at 06:57

score 0 · Answer 1 · answered Apr 21 '21 at 09:15

Based on your code and assuming first row in your sheet is never empty you could do something like that

Sub Attendance_Manday()
    Dim sht1 As Worksheet
    Dim row_count As Long, col_count As Long
    Dim mainrange As Range
    Dim startcell As Range
    Set startcell = Range("B1")
    Set sht1 = Sheets("Mandays")
    row_count = Sheets("Mandays").Cells(Rows.Count, startcell.Column).End(xlUp).Row
    col_count = Sheets("Mandays").Cells(startcell.Row, Columns.Count).End(xlToLeft).Offset(1, -2).Column
    Set mainrange = sht1.Range(startcell.Address & ":" & sht1.Cells(row_count, col_count).Address)
    
    
    Dim i As Long
    For i = 1 To col_count - 1
        mainrange.AutoFilter field:=i, Criteria1:="="
    Next i
    
    Dim rg As Range
    Set rg = mainrange.SpecialCells(xlCellTypeVisible)
    mainrange.AutoFilter

    rg.Rows.EntireRow.Hidden = True
    rg.Rows(1).EntireRow.Hidden = False
    
    
        
End Sub

An if you turn off screenupdating etc. it should be pretty fast as well

Hiding Multiple Rows with one Command - VBA

1 Answers1