simple code to show a number of rows

[MOB]

Rows 11-30 and rows 41-60 both are 20 rows (obviously)

I need a bit of code that when a user enters a number in cell A1, for example 5, the first 5 rows of each of the above 2 sections is visible, the last 15 rows are hidden

Any help would be great

TIA

 

[mohadin]

Hi
If I understand

Sub test()
    r1 = 11: lr1 = 30
    r2 = 41: lr2 = 60
    Cells(1).Offset(r1 - 1 + Cells(1, 1).Value).Resize(lr1 - r1 - 5 + 1).EntireRow.Hidden = True
    Cells(1).Offset(r2 - 1 + Cells(1, 1).Value).Resize(lr2 - r2 - 5 + 1).EntireRow.Hidden = True
End Sub

 

[steve the fish]

Here is a change event one to consider.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, rng2 As Range, numRows As Long

If Not Intersect(Target, Range("A1")) Is Nothing Then
    If IsNumeric(Range("A1")) Then
        numRows = Application.Max(0, Application.Min(20, Range("A1")))
        Set rng = Rows("11:30")
        Set rng2 = Rows("41:60")
        Union(rng, rng2).EntireRow.Hidden = False
        Select Case numRows
            Case 0
                Union(rng, rng2).EntireRow.Hidden = True
            Case 20
            Case Else
                rng.Offset(numRows).Resize(20 - numRows).EntireRow.Hidden = True
                rng2.Offset(numRows).Resize(20 - numRows).EntireRow.Hidden = True
        End Select
    End If
End If

End Sub

 

[jasonb75]

Perhaps

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
If Target.Address(0, 0) = "A1" Then
    On Error Resume Next
    Set r1 = Range("11:30"): Set r2 = Range("41:60")
    r1.EntireRow.Hidden = True
    r2.EntireRow.Hidden = True
    r1.Resize(Target.Value).EntireRow.Hidden = False
    r2.Resize(Target.Value).EntireRow.Hidden = False
End If
End Sub

 

[MOB]

Thanks all

For the worksheet change codes, where do these need to be placed? I cant get either to do anything so I'm obviously doing something wrong!

 

[jasonb75]

For the worksheet change codes, where do these need to be placed?

They need to go into the correct worksheet module.

From excel, right click the sheet tab (i.e. Sheet1), then 'view code'.

 

[MOB]

Many thanks, all working well now