CODEWITHSUNDEEP
vbaexcel
heavyarms
heavyarms

Reputation: 155

VBA code to hide a number of fixed discrete rows across a few worksheets

I'm for a solution to part of a macro I'm writing that will hide certain (fixed position) rows across a few different sheets. I currently have:

Sheets(Sheet1).Range("5:20").EntireRow.Hidden = True

To hide rows 5-20 in Sheet1. I also would like to hide (for arguements sake), row 6, row 21, and rows 35-38 in Sheet2 - I could do this by repeating the above line of code 3 more times; but am sure there's a better way of doing this, just as a learning exercise.

Any help much appreciated :)

Chris

Upvotes: 2

Views: 8698

Answers (3)

Tony Dallimore
Tony Dallimore

Reputation: 12403

This is a crude solution: no validation, no unhiding of existing hidden rows, no check that I have a sheet name as first parameter, etc. But it demonstrates a technique that I often find useful.

I load an array with a string of parameters relevant to my current problem and code a simple loop to implement them. Look up the sub and function declarations and read the section on ParamArrays for a variation on this approach.

Option Explicit
Sub HideColumns()

  Dim InxPL As Integer
  Dim ParamCrnt As String
  Dim ParamList() As Variant
  Dim SheetNameCrnt As String

  ParamList = Array("Sheet1", 1, "5:6", "Sheet2", 9, "27:35")

  SheetNameCrnt = ""

  For InxPL = LBound(ParamList) To UBound(ParamList)
    ParamCrnt = ParamList(InxPL)
    If InStr(ParamCrnt, ":") <> 0 Then
      ' Row range
      Sheets(SheetNameCrnt).Range(ParamCrnt).EntireRow.Hidden = True
    ElseIf IsNumeric(ParamCrnt) Then
      ' Single Row
      Sheets(SheetNameCrnt).Range(ParamCrnt & ":" & _
                                          ParamCrnt).EntireRow.Hidden = True
    Else
      ' Assume Sheet name
      SheetNameCrnt = ParamCrnt
    End If
  Next

End Sub

Upvotes: 2

chris neilsen
chris neilsen

Reputation: 53135

Specify a Union of some ranges as follows

With Sheet1
    Union(.Range("1:5"), .Rows(7), .Range("A10"), .Cells(12, 1)).EntireRow.Hidden = True
End With

Upvotes: 5

JMax
JMax

Reputation: 26591

Here is a try:

Sub hideMultiple()
    Dim r As Range
    Set r = Union(Range("A1"), Range("A3"))
    r.EntireRow.Hidden = True
End Sub

But you cannot Union range from several worksheets, so you would have to loop over each worksheet argument.

Upvotes: 4

Related Questions