insert rows automatically

[petee]

I have a spreadsheet where I need to copy a row and have EXCEL
automatically copy new rows based on a number inputted by the user
(less 1).

I use a code which was previously posted that creates a box asking how
many rows I need. This works fine but the problem is that people often
input one row too many
For example: If they need 10 rows overall , they inadvertently input a
10 ... giving a total of 11 rows (the original row + the 10 NEW
rows) )

So I was wondering if anyone would know how to fine tune the code so
that it would give me one less row than what the user inputs.

Here's the code that I use:

Sub copyrows()
numrows = InputBox("Number of rows")
'Rows(ActiveCell.Row).Copy ActiveCell.Resize(numrows)
Rows(ActiveCell.Row).Copy
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
End Sub

Thanks for any Help

 

[GS]

After serious thinking petee wrote :

I have a spreadsheet where I need to copy a row and have EXCEL
automatically copy new rows based on a number inputted by the user
(less 1).

I use a code which was previously posted that creates a box asking how
many rows I need. This works fine but the problem is that people often
input one row too many
For example: If they need 10 rows overall , they inadvertently input a
10 ... giving a total of 11 rows (the original row + the 10 NEW
rows) )

So I was wondering if anyone would know how to fine tune the code so
that it would give me one less row than what the user inputs.

Here's the code that I use:

Sub copyrows()
numrows = InputBox("Number of rows")
'Rows(ActiveCell.Row).Copy ActiveCell.Resize(numrows)
Rows(ActiveCell.Row).Copy
ActiveCell.Resize(numrows).Insert
Application.CutCopyMode = False
End Sub

Thanks for any Help

You do realize that this is going to backfire for user who can DO THE
MATH, don't you?

 

[petee]

After serious thinking petee wrote :









You do realize that this is going to backfire for user who can DO THE
MATH, don't you?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Obviously this is a risk, but nevertheless, they've requested it.

Petee

 

[GS]

petee presented the following explanation :

Obviously this is a risk, but nevertheless, they've requested it.

Petee

Okay then, lets go with the obvious approach first...

numrows = InputBox("Number of Rows") - 1

Though I suggest you test the user's input to be sure it's a number
(for one thing), and that it's greater than zero. In this case I'd go
with Excel's InputBox rather than VBa's.

========================
Dim lRows As Long
Const lMin As Long = 1
Const sPrompt As String = "Enter the number of rows to insert"
lRows = Application.InputBox(Prompt:=sPrompt, Type:=xlNumbers)
If lRows < 1 Then Exit Sub
If lRows > lMin Then lRows = lRows - 1
========================

Excel will handle validation of user input. If the user enters 1 then
that's what gets used, otherwise 1 less than the amount entered gets
used. I recommend a minimum level before user input gets adjusted since
it's reasonable to assume, for example, that 2 or 3 is likely. More
might be questionable, though.

 

[petee]

petee presented the following explanation :






Okay then, lets go with the obvious approach first...

  numrows = InputBox("Number of Rows") - 1

Though I suggest you test the user's input to be sure it's a number
(for one thing), and that it's greater than zero. In this case I'd go
with Excel's InputBox rather than VBa's.

========================
  Dim lRows As Long
  Const lMin As Long = 1
  Const sPrompt As String = "Enter the number of rows to insert"
  lRows = Application.InputBox(Prompt:=sPrompt, Type:=xlNumbers)
  If lRows < 1 Then Exit Sub
  If lRows > lMin Then lRows = lRows - 1
========================

Excel will handle validation of user input. If the user enters 1 then
that's what gets used, otherwise 1 less than the amount entered gets
used. I recommend a minimum level before user input gets adjusted since
it's reasonable to assume, for example, that 2 or 3 is likely. More
might be questionable, though.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Thanks for the Help Garry !

You're quite right, entering a 1 does create a problem.
However, being a newby in VBA, I'm not quite sure what you mean by
using '' Excel's InputBox rather than VBa's ''... what's the
difference ?

Also, what to do with the new code that you wrote ;

1) Create a NEW module ? ... I did this but no New rows are added
after I enter the amount of rows.
or
2) Insert it in the original code that I had ... and where ?

Thanks again for any help

 

[GS]

petee formulated on Thursday :

Thanks for the Help Garry !

You're quite right, entering a 1 does create a problem.
However, being a newby in VBA, I'm not quite sure what you mean by
using '' Excel's InputBox rather than VBa's ''... what's the
difference ?

The difference is that with VBa's InputBox you have to do your own
validation. Excel's InputBox is much more flexible. With Excel's
InputBox you can restrict the type of data entered, including selection
of ranges.

Also, what to do with the new code that you wrote ;

1) Create a NEW module ? ... I did this but no New rows are added
after I enter the amount of rows.
or
2) Insert it in the original code that I had ... and where ?

The code sample only handles getting the number of rows to insert from
the user. I'd replace your code for that with my sample. Not sure why
you copy an existing row rather than insert new rows of the same format
as the row above, but here's my version of your code sample:

Sub CopyRows()
Dim lRows As Long
Const lMin As Long = 1
Const sPrompt As String = "Enter the number of rows to insert"

lRows = Application.InputBox(Prompt:=sPrompt, Type:=xlNumbers)
If lRows < 1 Then Exit Sub
If lRows > lMin Then lRows = lRows - 1

With ActiveCell
.EntireRow.Copy: .Resize(lRows).Insert
End With
Application.CutCopyMode = False
End Sub

Just decide what the minimum number of rows to accept is before
changing the count by minus 1, and edit the Const statement
accordingly.

 

[Clif McIrvin]

petee formulated on Thursday :

The difference is that with VBa's InputBox you have to do your own
validation. Excel's InputBox is much more flexible. With Excel's
InputBox you can restrict the type of data entered, including
selection of ranges.

<...>

I'd never realized that! I'm guessing that's why I was never able to get
range selection to work properly way back when before I finally gave up!
Now, when I find that box of round tuit's I may revisit that macro
<grin>.

 

[GS]

Clif McIrvin has brought this to us :

<...>

I'd never realized that! I'm guessing that's why I was never able to get
range selection to work properly way back when before I finally gave up! Now,
when I find that box of round tuit's I may revisit that macro <grin>.

Yep! I don't use it much for range input but it's great for validating
user entries with no code from me to do so!