I'm pretty good with Access VBA but new to Excel.
Isn't there a simple way to go to a cell and insert x number of rows based
on an integer variable.

The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
insert is intAInsert.

I can't believe one must do what I'm seeing in this discussion group for
something that simple. Help me please.

Thanks,
Robin

Something like this?

ActiveSheet.Rows(rgWTBaEnd & ":" rgWTBaEnd + intAInsert-1).Select
Selection.Insert Shift:=xlDown

This will insert the new rows ABOVE row rgWTBaEnd. If you want to insert
the new rows beneath that row, use:

ActiveSheet.Rows(rgWTBaEnd + 1 & ":" rgWTBaEnd + intAInsert).Select
Selection.Insert Shift:=xlDown

HTH,

Eric

"Robin" wrote:

> I'm pretty good with Access VBA but new to Excel.
> Isn't there a simple way to go to a cell and insert x number of rows based
> on an integer variable.
>
> The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> insert is intAInsert.
>
> I can't believe one must do what I'm seeing in this discussion group for
> something that simple. Help me please.
>
> Thanks,
> Robin

Oops - left out the second "&" after the ":" in the formulas. I corrected
below.

"EricG" wrote:

> Something like this?
>
> ActiveSheet.Rows(rgWTBaEnd & ":" & rgWTBaEnd + intAInsert-1).Select
> Selection.Insert Shift:=xlDown
>
> This will insert the new rows ABOVE row rgWTBaEnd. If you want to insert
> the new rows beneath that row, use:
>
> ActiveSheet.Rows(rgWTBaEnd + 1 & ":" & rgWTBaEnd + intAInsert).Select
> Selection.Insert Shift:=xlDown
>
> HTH,
>
> Eric
>
> "Robin" wrote:
>
> > I'm pretty good with Access VBA but new to Excel.
> > Isn't there a simple way to go to a cell and insert x number of rows based
> > on an integer variable.
> >
> > The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> > insert is intAInsert.
> >
> > I can't believe one must do what I'm seeing in this discussion group for
> > something that simple. Help me please.
> >
> > Thanks,
> > Robin

Robin,

I have no idea what you mean by

> The cell I want to go to is rgWTBaEnd

Perhaps you could explain a bit more but to insert n rows use this

intAInsert = 5
Rows(7).Resize(intAInsert).Insert

This inserts 5 rows a row 7

Mike


"Robin" wrote:

> I'm pretty good with Access VBA but new to Excel.
> Isn't there a simple way to go to a cell and insert x number of rows based
> on an integer variable.
>
> The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> insert is intAInsert.
>
> I can't believe one must do what I'm seeing in this discussion group for
> something that simple. Help me please.
>
> Thanks,
> Robin

This is what I use. Hope this helps! If so, let me know, click "YES" below.

' adds new rows for new data
Sheets("Sheet1").Rows(rgWTBaEnd.Row).Resize(intAInsert).EntireRow.Insert


--
Cheers,
Ryan


"Robin" wrote:

> I'm pretty good with Access VBA but new to Excel.
> Isn't there a simple way to go to a cell and insert x number of rows based
> on an integer variable.
>
> The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> insert is intAInsert.
>
> I can't believe one must do what I'm seeing in this discussion group for
> something that simple. Help me please.
>
> Thanks,
> Robin

Mike H,

rgWTBaEnd is a range name. I use range names so if the layout of a
worksheet changes I don't have to change any cell addresses hard keyed into
programming lines. Sorry for the confusion.

I tried you suggestion and and inserted "rgrgWTBaEnd" (in quotes) where you
had the 7. I got a Run TIme Error 13 - Type Mismatch.

I'll try one of the other suggestions but play around with yours. It DOES
seem it should work...from my limited experience!

Thank you,
Robin

"Mike H" wrote:

> Robin,
>
> I have no idea what you mean by
>
> > The cell I want to go to is rgWTBaEnd
>
> Perhaps you could explain a bit more but to insert n rows use this
>
> intAInsert = 5
> Rows(7).Resize(intAInsert).Insert
>
> This inserts 5 rows a row 7
>
> Mike
>
>
> "Robin" wrote:
>
> > I'm pretty good with Access VBA but new to Excel.
> > Isn't there a simple way to go to a cell and insert x number of rows based
> > on an integer variable.
> >
> > The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> > insert is intAInsert.
> >
> > I can't believe one must do what I'm seeing in this discussion group for
> > something that simple. Help me please.
> >
> > Thanks,
> > Robin

EricG,

After trying Mike H's below I trie yours and got the same Run Time Error 13.
The rgWTBaEnd is a range name which refers to a single cell. Yours, as
MikeH's seems like it should work. Coming from Access VBA, it's these
references in Excel that are really confusing. Is the & ":" & in your line
trying to create a range from cells as you would do if you were keying in a
range in a formula? I tried removing that and the second reference to my
range so it would only refer to the single cell range. Putting the range
name in quotes gives me the Error 13 above. If I remove the quotes I get Run
Time error 1004 - Application Defined or Object...

Sorry I'm not getting what is probably simple. Or is it my reference to a
range name that is throwing everything off? I just assumed I could use range
names in VBA.

Thank you,
Robin

"EricG" wrote:

> Oops - left out the second "&" after the ":" in the formulas. I corrected
> below.
>
> "EricG" wrote:
>
> > Something like this?
> >
> > ActiveSheet.Rows(rgWTBaEnd & ":" & rgWTBaEnd + intAInsert-1).Select
> > Selection.Insert Shift:=xlDown
> >
> > This will insert the new rows ABOVE row rgWTBaEnd. If you want to insert
> > the new rows beneath that row, use:
> >
> > ActiveSheet.Rows(rgWTBaEnd + 1 & ":" & rgWTBaEnd + intAInsert).Select
> > Selection.Insert Shift:=xlDown
> >
> > HTH,
> >
> > Eric
> >
> > "Robin" wrote:
> >
> > > I'm pretty good with Access VBA but new to Excel.
> > > Isn't there a simple way to go to a cell and insert x number of rows based
> > > on an integer variable.
> > >
> > > The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> > > insert is intAInsert.
> > >
> > > I can't believe one must do what I'm seeing in this discussion group for
> > > something that simple. Help me please.
> > >
> > > Thanks,
> > > Robin

Hello Ryan H,

After trying the above responses to no avail, I've now tried yours an I get
Run Time Error 424 - Object Required. I replaced "Sheet 1" in your example
with "WTB" (the name of the worksheet that has range rgWTBaEnd on it.) Three
tries, three misses. I'm feeling a bit dumb. But you now understand why I
"Titled" my post as I did!

Thank you,
Robin

"Ryan H" wrote:

> This is what I use. Hope this helps! If so, let me know, click "YES" below.
>
> ' adds new rows for new data
> Sheets("Sheet1").Rows(rgWTBaEnd.Row).Resize(intAInsert).EntireRow.Insert
>
>
> --
> Cheers,
> Ryan
>
>
> "Robin" wrote:
>
> > I'm pretty good with Access VBA but new to Excel.
> > Isn't there a simple way to go to a cell and insert x number of rows based
> > on an integer variable.
> >
> > The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> > insert is intAInsert.
> >
> > I can't believe one must do what I'm seeing in this discussion group for
> > something that simple. Help me please.
> >
> > Thanks,
> > Robin

Robin,

In that case the syntax is

intAInsert = 5
Range("rgWTBaEnd").Resize(intAInsert).EntireRow.Insert

Mike

"Robin" wrote:

> Mike H,
>
> rgWTBaEnd is a range name. I use range names so if the layout of a
> worksheet changes I don't have to change any cell addresses hard keyed into
> programming lines. Sorry for the confusion.
>
> I tried you suggestion and and inserted "rgrgWTBaEnd" (in quotes) where you
> had the 7. I got a Run TIme Error 13 - Type Mismatch.
>
> I'll try one of the other suggestions but play around with yours. It DOES
> seem it should work...from my limited experience!
>
> Thank you,
> Robin
>
> "Mike H" wrote:
>
> > Robin,
> >
> > I have no idea what you mean by
> >
> > > The cell I want to go to is rgWTBaEnd
> >
> > Perhaps you could explain a bit more but to insert n rows use this
> >
> > intAInsert = 5
> > Rows(7).Resize(intAInsert).Insert
> >
> > This inserts 5 rows a row 7
> >
> > Mike
> >
> >
> > "Robin" wrote:
> >
> > > I'm pretty good with Access VBA but new to Excel.
> > > Isn't there a simple way to go to a cell and insert x number of rows based
> > > on an integer variable.
> > >
> > > The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> > > insert is intAInsert.
> > >
> > > I can't believe one must do what I'm seeing in this discussion group for
> > > something that simple. Help me please.
> > >
> > > Thanks,
> > > Robin

Use this if you want to insert an entire row.

Sheets("WTB").Rows(Range("rgWTBaEnd").Row).Resize(intAInsert).EntireRow.Insert

Or if you want to insert rows into the range only then:

Sheets("WTB").Rows(Range("rgWTBaEnd").Row).Resize(intAInsert).Insert

Hope this helps! If so, let me know, click "YES" below.
--
Cheers,
Ryan


"Robin" wrote:

> Hello Ryan H,
>
> After trying the above responses to no avail, I've now tried yours an I get
> Run Time Error 424 - Object Required. I replaced "Sheet 1" in your example
> with "WTB" (the name of the worksheet that has range rgWTBaEnd on it.) Three
> tries, three misses. I'm feeling a bit dumb. But you now understand why I
> "Titled" my post as I did!
>
> Thank you,
> Robin
>
> "Ryan H" wrote:
>
> > This is what I use. Hope this helps! If so, let me know, click "YES" below.
> >
> > ' adds new rows for new data
> > Sheets("Sheet1").Rows(rgWTBaEnd.Row).Resize(intAInsert).EntireRow.Insert
> >
> >
> > --
> > Cheers,
> > Ryan
> >
> >
> > "Robin" wrote:
> >
> > > I'm pretty good with Access VBA but new to Excel.
> > > Isn't there a simple way to go to a cell and insert x number of rows based
> > > on an integer variable.
> > >
> > > The cell I want to go to is rgWTBaEnd and the number of rows I wnat to
> > > insert is intAInsert.
> > >
> > > I can't believe one must do what I'm seeing in this discussion group for
> > > something that simple. Help me please.
> > >
> > > Thanks,
> > > Robin