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