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

Using VBA to select dynamic number of rows
 

How about an example(s) of before and after

--
Don Guillett
SalesAid Software

"Chad81" wrote in message
...
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

Using VBA to select dynamic number of rows
 

Nevermind I figured it out.... I changed it to

Range(Rows(Rowct), Rows(rowct2)).Select
where Rowct = user input and Rowct = the starting cell

Thanks for your time!

"Don Guillett" wrote:

How about an example(s) of before and after

--
Don Guillett
SalesAid Software

"Chad81" wrote in message
...
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

Using VBA to select dynamic number of rows
 

Now you need to work on getting rid of SELECTIONS.

range("a1").select
selection.copy
range("b1").select
selection.paste

can, and should be,

range("a1").copy range("b1")

--
Don Guillett
SalesAid Software

"Chad81" wrote in message
...
Nevermind I figured it out.... I changed it to

Range(Rows(Rowct), Rows(rowct2)).Select
where Rowct = user input and Rowct = the starting cell

Thanks for your time!

"Don Guillett" wrote:

How about an example(s) of before and after

--
Don Guillett
SalesAid Software

"Chad81" wrote in message
...
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