Macro to hide rows ad hoc

[detweiler]

I found this - -

Worksheets(“Sheet1”).Range(“RowNumber1:RowNumber2”).EntireRow.Hidden = True​

- - and would work, but would have to modify it each time based on the worksheet I'm in. Yes, right-clicking is easy, but when there are hundreds or, and a couple of times, thousands, no so much.

Is it possible to modify the current worksheet ( active.worksheet ? ) and be prompted for what rows I want to hide?

Thanks for any guidance, help and insight.

 

[Fluff]

Would you always be hiding consecutive rows?

 

[Peter_SSs]

Something like this?

Sub Hide_Rows()
  Dim sRws As String
  
  sRws = InputBox("Enter rows to be hidden, separated by a colon. eg 5:25 or for a single row just 3")
  On Error Resume Next
  Rows(sRws).Hidden = True
  On Error GoTo 0
End Sub

 

[detweiler]

Yes, and can be in multiple places throughout a single worksheet.

 

[detweiler]

Thank you. Will give this a go and let you know how well it worked!

 

[Fluff]

How about

Sub detweiler()
   Dim Rws As String
   Dim Sp As Variant
   Dim i As Long
   Rws = InputBox("Please enter rows to hide like 3:20 or if multiple sections 3:20,25:30")
   If InStr(1, Rws, ",") > 0 Then
      Sp = Split(Rws, ",")
      For i = 0 To UBound(Sp)
         Rows(Sp(i)).Hidden = True
      Next i
   Else
      Rows(Rws).Hidden = True
   End If
End Sub

 

[Peter_SSs]

How about

Sub detweiler()
   Dim Rws As String
   Dim Sp As Variant
   Dim i As Long
   Rws = InputBox("Please enter rows to hide like 3:20 or if multiple sections 3:20,25:30")
   <del>If InStr(1, Rws, ",") > 0 Then</del>
      Sp = Split(Rws, ",")
      For i = 0 To UBound(Sp)
         Rows(Sp(i)).Hidden = True
      Next i
   <del>Else
      Rows(Rws).Hidden = True
   End If</del>
End Sub

Probably don't really need those crossed out rows.

.. or could be compacted to ..

Sub detweiler_v2()
  Dim Sp As Variant
  
  For Each Sp In Split(InputBox("Please enter rows to hide like 3:20 or if multiple sections 3:20,25:30"), ",")
    Rows(Sp).Hidden = True
  Next Sp
End Sub

 

[detweiler]

Thank you for the input. The multiples option makes going through each worksheet quicker.

 

[detweiler]

Appreciate and enjoy the simplicity of the code sir. It works as I need it to, as I said to Fluff, it makes going through each worksheet quicker now.

 

[Fluff]

Glad we could help & thanks for the feedback.

Probably don't really need those crossed out rows.

Excellent point as always.