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" <(E-Mail Removed)> wrote in message
news:83CA8E54-5D11-4E85-987C-(E-Mail Removed)...
>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
> >
> >