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

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



"tpeter" wrote in message
...
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

not tested, but maybe like this:

Sub TEST002()

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

"tpeter" wrote:

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

Patrick,

Worked Great, thank you so much for your help.

"Patrick Molloy" wrote:

not tested, but maybe like this:

Sub TEST002()

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

"tpeter" wrote:

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