Create Function based on cell value

T

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
 
J

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
 
P

Patrick Molloy

not tested, but maybe like this:

Sub TEST002()

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top