Macro to hide rows ad hoc

detweiler

Board Regular
Joined
Aug 2, 2013
Messages
60
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Would you always be hiding consecutive rows?
 
Upvote 0
Something like this?
Code:
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
 
Upvote 0
How about
Code:
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
 
Upvote 0
How about
Code:
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 ..
Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top