Create Function based on cell value
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
|