Hide Multiple number of rows based on a value

Donna216

New Member
Joined
Aug 20, 2019
Messages
3
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!!!!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Is 1000 an arbitary amount.....I'm assuming it is the last row of data on the "Testing" sheet ?
 
Upvote 0
How about

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
Ok, if line 1000 is fixed you can use

Code:
Sub MM1()
 Dim S As Range
 Set S = Sheets("Summary").Range("H10")
 Sheets("Testing Records").Rows(S+1 & ":1000").EntireRow.Hidden = True
End Sub
 
Last edited:
Upvote 0
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.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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