Create Function based on cell value

[tpeter]

I have a spreadsheet with multiple tabs on it. Each tab contains a form to
fill out, this form is dianamic in creating the number of rows a user wants
for each instance. A message box comes up asking them "how many rows would
you like?" The code then inserts the correct number of lines, here is that
code:

Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
RANGE("z3").Value = TextBox6.Value
Call Plant_1001
End Sub ' textbox to store value

Sub Plant_1001()
RANGE("Plt_1001").Select
Selection.Copy
RANGE("Plt_1001").Resize(RANGE("z3") - 1).Insert Shift:=xlDown
Application.CutCopyMode = False
End Sub ' code to run on close of form

This works great but now I need to be able to delete the rows if someone
makes a mistake (currently just inserts more rows). I have come up with:

Sub TEST001()
'
' TEST001 Macro
'

RANGE("plt_1001").Select
Selection.Offset(-1).Select
Selection.Delete Shift:=xlUp
End Sub

This code currently finds plt_1001 goes up one row and deletes it. But what
I really need is for it to reference cell "z3", make it a negative number,
and delete that many rows above. This should work as long as the user doesn't
insert any rows manually.

Thank everyone for there help.

Tim Peter

 

[JLGWhiz]

Sub dk()

Range("Plt_1001").Copy
Range("Plt_1001").Resize(Range("Z3") - 1) _
.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("Plt_1001").Offset(-Range("Z3") + 1) _
.Resize(4).EntireRow.Delete
End Sub

You could make look better with:

rwCnt = Range("Z3").Value
Range("Plt_1001").Copy
Range("Plt_1001").Resize(rwCnt - 1).Insert Shift:=xlDown
Application.CutCopyMode = False
Range("Plt_1001").Offset(-rwCnt + 1).Resize.EntireRow.Delete

 

[Patrick Molloy]

not tested, but maybe like this:

Sub TEST002()

RANGE("plt_1001").Offset(-range("z3")).Resize(range("z3")).Delete Shift:=xlUp
End Sub

 

[tpeter]

Patrick,

Worked Great, thank you so much for your help.