Macro to hide and unhide rows depending on value in a precise cell

Hi all,

I've been researching for an answer but didn't find one solving my problem.

Context: I'm building a file for people to enter all their team members names in a table. I want the table to be tailored to the number of team members : let's say that they enter in cell A1 the number of members in their team. If they insert the number 5 in A1: the table would be 5 lines. If they insert the number 30, the table would be 30 lines etc. Simplest way is then to pre-create the table and to build a macro to hide lines based on cell A1 value.

So in this context could you please explain how to create this macro?

Imagine the table goes from row 6 to row 100 and column A to H (all team members name would be in column A of the table)

If user enters a value in A1, and if value is 1: rows 7 to 100 would be hidden (leaving only line 6 in the table, for 1 team member), if the value entered in A1 is 2: row 8 to 100 would be hidden (leaving 2 lines), etc and etc.

I guess I would need to create a variable but I'm a bit lost here.

Can someone help?

Quote from Justin Doward

Hi DJ,

Your scenario would be a lot easier to "imagine" if you provided an example sheet. In what you have written you are suggesting a column full of names, in column A but then you want to hide and show column A. What is happening to the names here? Do you just want a list of names, each name can have any number of lines inserted between one name and the next depending on a selected team size?

Whatever the scenario an example sheet will save a lot of time.

thanks for the answer,

Here is a file attached illustrating what I mean. Hope it's clearer,

Quote from royUK

Why do you need to hide rows? A Table allows you to add data and hit tab to add a new row with all formulas and formats added.

Hi Roy, thanks for the answer. It's a document for general usage (including many people not familiar with excel): i'm trying to make it as user friendly (automatizing it) as much as possible.

Quote from royUK

This should do what you want. You just need to decide how to run it.

Code

Sub HideRows()
    If IsEmpty(Range("A1")) Then Exit Sub
    Dim iX As Integer
    iX = Range("A1").Value - 1
    Rows("4:23").EntireRow.Hidden = True
    Rows("4:" & iX + 4).EntireRow.Hidden = False
End Sub

it works that's great thank you!! Is there a way to trigger the Macro every time the cell A1 value is changed?

Quote from royUK

This should do what you want. You just need to decide how to run it.

Code

Sub HideRows()
    If IsEmpty(Range("A1")) Then Exit Sub
    Dim iX As Integer
    iX = Range("A1").Value - 1
    Rows("4:23").EntireRow.Hidden = True
    Rows("4:" & iX + 4).EntireRow.Hidden = False
End Sub

it's ok, just found out how to trigger it! thanks again!