Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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(intAIn sert).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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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(intAIn sert).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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
Robin,
In that case the syntax is intAInsert = 5 Range("rgWTBaEnd").Resize(intAInsert).EntireRow.In sert 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
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(intAIn sert).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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
Mike,
That works! Thank you very much! Ryan's was almost there also, just replace the 'Rows(...' with 'Range(..." I'm gonna have to work on these reference differences. Thank you all, Robin "Mike H" wrote: Robin, In that case the syntax is intAInsert = 5 Range("rgWTBaEnd").Resize(intAInsert).EntireRow.In sert 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
Robin,
Glad I could help and thanks for the feedback. Mike "Robin" wrote: Mike, That works! Thank you very much! Ryan's was almost there also, just replace the 'Rows(...' with 'Range(..." I'm gonna have to work on these reference differences. Thank you all, Robin "Mike H" wrote: Robin, In that case the syntax is intAInsert = 5 Range("rgWTBaEnd").Resize(intAInsert).EntireRow.In sert 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
Thank you Ryan. Mike gave me an answer that works and your reply taught me
something if I'm understanding correctly. Your Resize...Insert example is like the menu option Insert...Cells...Shift Cells Down as opposed to the Resize...Insert.EntireRow which is a full row insert. That'll help in the future. Thanks again, Robin "Ryan H" wrote: 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(intAIn sert).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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
C'mon. Isn't there a SIMPLE way to...
Robin,
Sorry - I assumed rgWTBaEnd was a row number. I'm glad you were able to get the solution you needed from one of the other folks. Cheers, Eric "Robin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula-simple question; simple operator | Excel Discussion (Misc queries) | |||
Simple problem, simple formula, no FUNCTION ! | Excel Worksheet Functions | |||
Simple Simple Excel usage question | Excel Discussion (Misc queries) | |||
Make it more simple or intuitive to do simple things | Charts and Charting in Excel | |||
simple question, hopefully a simple answer! | Excel Programming |