VBA/Excel Issue: When I tried to hide specific multiple row ranges in a newly created workbook, i get a runtime 450 error

I have a an excel workbook that contains a macro that copies certain tabs into a new workbook as values and want to insert a small step within the VBA macro that hides (not collapses) specific rows across the 4 worksheets in the newly created workbook.

The majority of the macro works, but i am getting a Runtime ‘450’ error when i insert the code to hide the specific row ranges. The macro begins with copying the values into a new workbook

    Sheets(Array("Sheet1", "Sheet3", "Sheet2", "Sheet4")).Copy
    Sheets(Array("Sheet1", "Sheet3", "Sheet2", "Sheet4")).Select
    Sheets("Sheet1").Activate
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

' i removed some of the other lines of vba that do other formatting steps
' Here is where my issue begins: 

    Sheets("Sheet1").Select
    ActiveSheet.Rows("21:30, 41:42, 69:70, 76:87").EntireRow.Hidden = True
    
' i havent extended the logic to these three sheets as i have been trying to get it right once, but the next three rows will be updated to do the same thing
    Sheets("Sheet2").Rows("21:30", "41:42", "69:70", "76:93").EntireRow.Hidden = True
    Sheets("Sheet3").Rows("79:90", "106:113").EntireRow.Hidden = True
    Sheets("Sheet4").Rows("79:96", "114:119").EntireRow.Hidden = True

Ive tried a few solutions such as referencing the Sheets("Sheet1") instead of ‘ActiveSheet’, but tbh im not as proficient in writing VBA code as I am at reading it.

>Solution :

I think just changing the function "Rows" to "Range" should do the job, like:

From:

ActiveSheet.Rows("21:30, 41:42, 69:70, 76:87").EntireRow.Hidden = True

To:

ActiveSheet.Range("21:30, 41:42, 69:70, 76:87").EntireRow.Hidden = True

Leave a Reply