Hide Multiple number of rows based on a value

[Donna216]

Hey There - New to VBA & I need to workout how to hide a certain number of rows from a different worksheet based on the value inserted on another worksheet.

Details are:
* 'Summary' worksheet cell 'H10' the user inserts a value (e.g. 350), which is the # of records which will need to be completed in the 'Testing Records' worksheet
* 'Testing Records' worksheet data can be completed from row 5 to 1000
* Using the example value of 350, I want to then hide row 351 to 1000
* Also need to allow if they change the value amt (either decrease or increase) the rows visible will alter.

Hope the above makes sense!!!!

 

[Michael M]

Is 1000 an arbitary amount.....I'm assuming it is the last row of data on the "Testing" sheet ?

 

[Michael M]

How about

Sub MM1()
 Dim lr As Long, r As Long, S As Range
 lr = Sheets("Testing Records").Cells(Rows.Count, "A").End(xlUp).Row
 Set S = Sheets("Summary").Range("H10")
 Sheets("Testing Records").Rows(S+1 & ":" & lr).EntireRow.Hidden = True
End Sub

 

[Donna216]

Thank you - Line 1000 is the actual last row of the Testing Record summary sheet (it would never exceed this row) where data will be entered. As there is info after row 1000, I will always need to hide rows 1000 & prior.

 

[Michael M]

Ok, if line 1000 is fixed you can use

Sub MM1()
 Dim S As Range
 Set S = Sheets("Summary").Range("H10")
 Sheets("Testing Records").Rows(S+1 & ":1000").EntireRow.Hidden = True
End Sub

 

[Peter_SSs]

Hey There - New to VBA & I need to workout how to hide a certain number of rows from a different worksheet based on the value inserted on another worksheet.

Details are:
* 'Summary' worksheet cell 'H10' the user inserts a value (e.g. 350), which is the # of records which will need to be completed in the 'Testing Records' worksheet
* 'Testing Records' worksheet data can be completed from row 5 to 1000
* Using the example value of 350, I want to then hide row 351 to 1000
* Also need to allow if they change the value amt (either decrease or increase) the rows visible will alter.

Hope the above makes sense!!!!

Welcome to the MrExcel board!

The details and example you gave don't quite add up to me. You said on Testing Records that data can be entered from row 5. With your example of 350 you said rows 351 to 1000 should be hidden. However, that would leave record rows 5:350 visible, but that is only 346 record rows, not the 350 being the number of records that need to be completed.

In any case give this Worksheet_Change event code a try. To implement ..
1. Right click the Summary sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by entering/changing the value in H10 on the Summary sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RecordsVis As Long
  
  If Not Intersect(Target, Range("H10")) Is Nothing Then
    RecordsVis = Range("H10").Value
    With Sheets("Testing Records")
      .Rows("5:1000").Hidden = False
      If RecordsVis < 996 Then
        .Rows(5 + RecordsVis & ":1000").Hidden = True
      End If
    End With
  End If
End Sub