VBA/Excel Issue: When I tried to hide specific multiple row ranges in a newly created workbook, i ge-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

You are providing the wrong number of parameters to the .Rows method, which is why you get that 450 runtime error. Rows can take a single parameter and you are passing in 4 in the first call, and 2 in the other 2 calls. I think you just misplaced quotes and should use the Range method instead of Rows if you want to capture multiple disjointed rows:

Sheets("Sheet2").Range("21:30, 41:42, 69:70, 76:93").EntireRow.Hidden = True
Sheets("Sheet3").Range("79:90, 106:113").EntireRow.Hidden = True
Sheets("Sheet4").Range("79:96, 114:119").EntireRow.Hidden = True