View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Chad81 Chad81 is offline
external usenet poster
 
Posts: 3
Default Using VBA to select dynamic number of rows

I am trying to write a macro that will pass a value from an input form to VBA
code to insert rows. I currently have a sheet with 100 rows and I have a
button on the sheet that adds a row for each additional item needed. The
problem is that when I need to add a bunch of rows the code takes a while to
run, so I would like it to select more than one row @ a time to copy and
insert.

I have the following code. Does anyone know how to make the piece of code
"Rows(Rowct).Select" select a number of rows that is dependent on what the
user enters into the form?

Application.ScreenUpdating = False

Sheets("Baseline").Select
Range("AL2").Value = txtcat.Value
Rowct = Range("itemcounter").Value
txtcat.Value = "0"
frmI.Hide

Dim lCount As Long
Sheets("Baseline").Select
Rowct = Range("itemcounter").Value

If Sheets("Baseline").Range("AL1").Text = "1" Then
lCount = 0
Do Until lCount = Range("AL2").Value
lCount = lCount + 1
Rows(Rowct).Select
Selection.COPY
Rows(Rowct + 1).Select
Selection.Insert Shift:=xlDown
Application.CutCopyMode = False
Range("A11").Select
Loop