duplicating rows macro

[abanky]

I have a row of calculations that use cell A2 as the initial value. The calculations go from B2 to AZ2. All cells have a calculation that relies on the calculation in the cells before it.

Sometimes i have multiple calculations to run, so I drag A2:AZ2 down. So if i have 10 calculations to do, I now have the matrix A2:AZ11 set up. The only cells that are inputs are in Column A.

My question is can i set up a macro where if i put 5 in say cell A1, excel automatically drags these cells down to from my 5 row matrix?

 

[abanky]

any ideas?

 

[Fluff]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) <> "A1" Then Exit Sub
   Range("A2:AZ" & Target.Value + 1).Filldown
End Sub

 

[abanky]

Perfect thank you!!!

What if i wanted the macro to be more dynamic? Currently, if you type 1 in there, the whole row 2 disappears. Also, how can it be changed so that if i first type 10, I get nine additional rows, but if I change my mind to 8, then two rows get erased?

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) <> "A1" Then Exit Sub
   If Target.Value > 1 Then
      Range("3:" & Rows.Count).ClearContents
      Range("A2:AZ" & Target.Value + 1).Filldown
   End If
End Sub

 

[abanky]

Perfect. Thank you so much.

 

[Fluff]

You're welcome