Jul 11 2019 08:53 PM
@Ingeborg Hawighorst This works perfectly! Thank you.
One last one you may be able to help me with.
I would like to hide rows on a sheet based on a number in a cell (A6).
Starting again with row 9 being the first row, so if number 1 is in the A6 cell then row 9 is shown,
if number 2 is in A6 then rows 9 and 10 are shown.
Continuing up to number 250 in A6 showing all rows up to row 258.
I would like rows 1-8 and all rows after 258 always visible and not effected but this formula.
I have not completed a function like this before and am not having much luck on Google.
Thank you.
Jul 12 2019 07:59 PM
You can do what you request using VBA code. The suggested sub runs automatically when you change the value of cell A6.
Make sure you put the sub in the code pane for the worksheet being watched. Rightclick its sheet tab, choose View Code... and paste the sub there. Make sure you save the workbook with .xls, .xlsm or .xlsb file extension.
'Put this sub in the code pane for the worksheet being watched Private Sub Worksheet_Change(ByVal Target As Range) Dim targ As Range, watch As Range Set targ = Me.Range("9:258") Set watch = Me.Range("A6") If Not Intersect(watch, Target) Is Nothing Then On Error Resume Next targ.EntireRow.Hidden = True targ.Resize(watch.Value).EntireRow.Hidden = False End If End Sub
Jul 14 2019 03:29 PM - edited Jul 14 2019 03:31 PM
Perfect! thank you for your help.