How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask JamesStone Your Own Question

JamesStone
JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience:  BA English Teaching, Data Analyst 7 Years
20612472
Type Your Question Here...
JamesStone is online now
A new question is answered every 9 seconds

I need help creating a macro that can insert a number of rows

Customer Question

I need help creating a macro that can insert a number of rows (given by an input box) to a sheet? I'd like to have a button, that when pressed, ask where to insert the row(s) (it will insert above the selected row), and how many rows to insert. Additionally, I want the formats and formulas copied from the row that was selected to insert above.

Can anybody help?
Submitted: 3 years ago.
Category: General
Expert:  JamesStone replied 3 years ago.
[Remember, if you like the answer, click Accept. Otherwise, feel free to ask for another expert! Thanks!]

Hello, based on your specifications, I have developed a VBA macro that does the following:

1: Which row do you want to start on?
2: How many rows do you want to shift and copy?

After you answer the question using whole numbers, it will select the row, add rows, copy the content and paste it up to your starting point.

To activate this, you can press ALT F8 to pull up the macros box or manually navigate through your excel menus to the macros, then run the
"RowShift" macro.

Download the sample file here, which contains the macro:
http://www.mediafire.com/?pa4em7cjme9bdm6

To test the macro, open the spreadsheet, run the macro, enter 11 as the first value, and 10 for the second value. It will shift the row 11 value down and copy its contents up.

JamesStone, Analyst, Programmer, Writer
Category: General
Satisfied Customers: 195
Experience: BA English Teaching, Data Analyst 7 Years
JamesStone and 59 other General Specialists are ready to help you
Customer: replied 3 years ago.
Could you possibly link it at wikisend... I can't access this here at work.
Customer: replied 3 years ago.
Also, I have 7 sheets I want to add this in.... can you tell me what I need to do to adopt it to my project.
Expert:  JamesStone replied 3 years ago.

Try this:

https://spreadsheets.google.com/ccc?key=0AlQQ_UXuFYukdF9lWlVReDNWX0JDOFZDRHg2bkNBSnc&hl=en

 

To take the macro from this spreadsheet and use it in another you can go into visual basic editor and copy the contents of the macro into the module on the new spreadsheet.

 

IE: Alt F11 to open up vb editor, double click module1, find the code starting with sub RowShift() and copy the whole thing. Open up the new spreadsheet, Alt F11, double click module1, paste. When you run the macro window it should show up as an option.

 

Alternatively, if you want to try it out on the new spreadsheet, as long as you have the test.xls spreadsheet open, the function should be "shared" between spreadsheets.

Customer: replied 3 years ago.

No, I'm sorry. The only thing that seems to work is wikisend?

 

Does this form have a button on the sheet? If not, can you add one?

Expert:  JamesStone replied 3 years ago.
OK try a direct download from me instead.
the button may or may not show up, as it may be linked to the software itself as opposed to the xls file.
If this is the case, simply create the button by following these easy steps:

1: Go to VIEW > Toolbars > Customize
2: Under COMMANDS go to MACROS then select CUSTOM BUTTON (should be a happy face). You can drag that up next to the HELP menu
3: Right click the happy face and select "ASSIGN MACRO" at the bottom.
4: Select the new macro, and then whenever you want to use it you just click the happy face.

http://www.bottingsecrets.com/ja/test.xls

I tested this from a different computer, and it worked just fine.
If for some reason you're having an issue with the button showing up, either due to version conflicts or some other reason, I also created a shortcut using CTRL + H as an alternative hotkey.
Customer: replied 3 years ago.

I like, thank you very much. Can you make it only copy formats and formulas, not the actual contents of the cells being copied?

 

Also, change the words to say "How many lines would you to insert?"

Expert:  JamesStone replied 3 years ago.
I went ahead and updated the prompt, however, so far the modifications to the VB to restrict the values pasted to formulas and formats is not taking.

If there were specific columns that were related to the formulas it might be feasible to hard code those in, but it would be a lot of additional work, and doesn't sound like it would be ideal if you've got 7 different sheets.

I'll try to see if I can look into this some more. I have a feeling it relates to the object being copied being a row as opposed to a range, and the variants of xlpasteformats and xlpasteformulas appears to apply to a range object.
Customer: replied 3 years ago.

Ok. I really need to do it, otherwise it will create more work. I guess I should've been more specific in my original question.

 

Thank you so much!

Customer: replied 3 years ago.

I'm having trouble loading you excel sheet macro into my workbook? I keep getting a "Sub or Function Not Defined" message? Please help.

 

Did you ever figure out how to do a paste special with a macro?

Expert:  JamesStone replied 3 years ago.
This is what you will need to paste in the visual basic under your vbaproject module:

Sub RowShift()
Dim StartRow As Integer
Dim UserValue As Integer
Dim FullValue As Integer
Dim X As Integer
X = 0
FullValue = 0

StartRow = InputBox("Which row do you want to start on?")
UserValue = InputBox("How many lines do you want to insert?")

Rows(StartRow).Select

Do Until X = UserValue
Selection.Rows.Insert (xlShiftDown)
X = X + 1
Loop

Rows(StartRow + UserValue).Select
Selection.Copy
Rows(StartRow & ":" & (StartRow + UserValue)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub


In regards to the pastespecial, I have incorporated the function into the macro. The closest option you have is to paste special with a "paste number formats and formulas" and "paste formats" which I went ahead included above, but you'll still end up pasting other values since it copies the entire row under this macro. Again, if there are specific columns that need to be copied as opposed to the entire row, it is feasible to go that route.

However, you'll need to supply those requirements, and they would need to apply universally to all of your sheets. OR, if they do not, then it might be feasible to develop a separate macro for each, or create a macro with additional prompts that specify the column or columns you want to have copied.

Personally, you may find it is much easier to just CTRL + SHIFT + ARROW DOWN to quickly select a columnar area you'd like to delete after the macro has done the majority of the work for you (you can also SHIFT + ARROW to select adjacent columnar ranges after your selection has been made to make short work of a range of data you want to wipe).

If you'd like to test what I'm talking about outside of the macro arena, copy a row then highlight another row and right click and "Paste special" and try the various function options. In cases where you want to copy the formula, you end up with values carrying over as well.

This macro addresses your original request to carry the formats and formulas of the row to those that are created above. If using control/shift/arrow commands to select and delete the ranges you didn't want copied is unacceptable, it would be useful to identify if there are always certain columns you don't want, then an extra step could be added to the macro that simply selects that range for you and deletes it. Again, depending on if your column selections to be deleted are consistent among sheets.
Customer: replied 3 years ago.

Okay, I have 4 different sheets I want to use this on.

 

The biggest sheet needs the values in these columns deleted after the rows are inserted:

 

A,B,C,F,G,I,J,K,L,M,N,O,P,Q,R,S,T,U

 

I can modify the code later for the other sheets.

 

Thanks,

Expert:  JamesStone replied 3 years ago.
Are the only fields being copied columns D, E, and H, with column U being the "end" ?
Customer: replied 3 years ago.

Actually, they are:

 

D,E,H,T,U,V through DM.

 

I had T & U in the columns to be deleted, but they actually need to be copied and pasted.

 

I would prefer go by which columns need to have the contents deleted.

Expert:  JamesStone replied 3 years ago.
A,B,C,F,G,I,J,K,L,M,N,O,P,Q,R,S deletion commands have been added.

I went ahead and formatted it in a way that you can easily reverse engineer it to apply to your other sheets. Just make sure to give the "Sub [name]" line something different so you can keep your macros separated from sheet to sheet.

In future versions, make sure to change both sections of the column reference...for example:

Range(Cells(StartRow, 1), Cells((StartRow + UserValue), 1)).Value = ""

The "1" refers to column A, and is referred to twice. To make it refer to column D in another macro, just change the 1's to 4's.

I used a value assignment of null as opposed to a delete command because that can shift cell contents sometimes.

Below is the code:
_______

Sub RowShift()
Dim StartRow As Integer
Dim UserValue As Integer
Dim FullValue As Integer
Dim X As Integer
X = 0
FullValue = 0

StartRow = InputBox("Which row do you want to start on?")
UserValue = InputBox("How many lines do you want to insert?")

Rows(StartRow).Select

Do Until X = UserValue
Selection.Rows.Insert (xlShiftDown)
X = X + 1
Loop

Rows(StartRow + UserValue).Select
Selection.Copy
Rows(StartRow & ":" & (StartRow + UserValue)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Range(Cells(StartRow, 1), Cells((StartRow + UserValue), 1)).Value = ""
Range(Cells(StartRow, 2), Cells((StartRow + UserValue), 2)).Value = ""
Range(Cells(StartRow, 3), Cells((StartRow + UserValue), 3)).Value = ""
Range(Cells(StartRow, 6), Cells((StartRow + UserValue), 6)).Value = ""
Range(Cells(StartRow, 7), Cells((StartRow + UserValue), 7)).Value = ""
Range(Cells(StartRow, 9), Cells((StartRow + UserValue), 9)).Value = ""
Range(Cells(StartRow, 10), Cells((StartRow + UserValue), 10)).Value = ""
Range(Cells(StartRow, 11), Cells((StartRow + UserValue), 11)).Value = ""
Range(Cells(StartRow, 12), Cells((StartRow + UserValue), 12)).Value = ""
Range(Cells(StartRow, 13), Cells((StartRow + UserValue), 13)).Value = ""
Range(Cells(StartRow, 14), Cells((StartRow + UserValue), 14)).Value = ""
Range(Cells(StartRow, 15), Cells((StartRow + UserValue), 15)).Value = ""
Range(Cells(StartRow, 16), Cells((StartRow + UserValue), 16)).Value = ""
Range(Cells(StartRow, 17), Cells((StartRow + UserValue), 17)).Value = ""
Range(Cells(StartRow, 18), Cells((StartRow + UserValue), 18)).Value = ""
Range(Cells(StartRow, 19), Cells((StartRow + UserValue), 19)).Value = ""

End Sub
Customer: replied 3 years ago.

I like, but it did not copy my conditional formats right. Do you know a way to copy conditional formats and not affect the formulas?

 

If not, that is okay.

 

Manually, this done by highlighting part of the row, then grabbing the corner and dragging down. Not sure how to do that in VBA?

 

Also, the row number that is entered as the location to insert, it's data is being deleted as well as the new inserted rows? I want to keep the original row's data.

Expert:  JamesStone replied 3 years ago.
I have made modifications to shift the content clearing up 1 row.
I have also modified the paste to include all cell attributes, and I tried stretching out the model row and seeing if the format carried with it, and it did when the content was pasted.

Alternatively, you can use the format brush to apply your formatting manually from your model cell or row to a large area. It is also feasible to physically program attributes to an affected range, but the full version copy should take care of that. You may want to verify that the conditional formatting settings aren't impacted by the change the macro makes to your spreadsheet as well.

At this point continuing to make modification after modification is going well beyond the scope of the original question, and I've honestly invested quite a bit more time than I was originally expecting to, simply because I want to be helpful.

This should address your latest round of changes:
______________________________
Sub RowShift()
Dim StartRow As Integer
Dim UserValue As Integer
Dim FullValue As Integer
Dim X As Integer
X = 0
FullValue = 0

StartRow = InputBox("Which row do you want to start on?")
UserValue = InputBox("How many lines do you want to insert?")

Rows(StartRow).Select

Do Until X = UserValue
Selection.Rows.Insert (xlShiftDown)
X = X + 1
Loop

Rows(StartRow + UserValue).Select
Selection.Copy
Rows(StartRow & ":" & (StartRow + UserValue)).Select
Paste
Range(Cells(StartRow, 1), Cells((StartRow + UserValue - 1), 1)).Value = ""
Range(Cells(StartRow, 2), Cells((StartRow + UserValue - 1), 2)).Value = ""
Range(Cells(StartRow, 3), Cells((StartRow + UserValue - 1), 3)).Value = ""
Range(Cells(StartRow, 6), Cells((StartRow + UserValue - 1), 6)).Value = ""
Range(Cells(StartRow, 7), Cells((StartRow + UserValue - 1), 7)).Value = ""
Range(Cells(StartRow, 9), Cells((StartRow + UserValue - 1), 9)).Value = ""
Range(Cells(StartRow, 10), Cells((StartRow + UserValue - 1), 10)).Value = ""
Range(Cells(StartRow, 11), Cells((StartRow + UserValue - 1), 11)).Value = ""
Range(Cells(StartRow, 12), Cells((StartRow + UserValue - 1), 12)).Value = ""
Range(Cells(StartRow, 13), Cells((StartRow + UserValue - 1), 13)).Value = ""
Range(Cells(StartRow, 14), Cells((StartRow + UserValue - 1), 14)).Value = ""
Range(Cells(StartRow, 15), Cells((StartRow + UserValue - 1), 15)).Value = ""
Range(Cells(StartRow, 16), Cells((StartRow + UserValue - 1), 16)).Value = ""
Range(Cells(StartRow, 17), Cells((StartRow + UserValue - 1), 17)).Value = ""
Range(Cells(StartRow, 18), Cells((StartRow + UserValue - 1), 18)).Value = ""
Range(Cells(StartRow, 19), Cells((StartRow + UserValue - 1), 19)).Value = ""

End Sub
Customer: replied 3 years ago.

Okay, if want to stop helping, I understand. I think my intentions from the start have stayed in tact, I've just had to see it work to see if it is what I wanted.

 

I'll throw you a bunus (I was going to anyway). Up to you.

 

The above code had an error. Where it says Paste, I believe it should say Selection.Paste. Also, when I run it, it gives me a "400" error.

Expert:  JamesStone replied 3 years ago.
Try this:


Sub RowShift()
Dim StartRow As Integer
Dim UserValue As Integer
Dim FullValue As Integer
Dim X As Integer
X = 0
FullValue = 0

StartRow = InputBox("Which row do you want to start on?")
UserValue = InputBox("How many lines do you want to insert?")

Rows(StartRow).Select

Do Until X = UserValue
Selection.Rows.Insert (xlShiftDown)
X = X + 1
Loop

Rows(StartRow + UserValue).Select
Selection.Copy
Rows(StartRow & ":" & (StartRow + UserValue)).Select
Selection.PasteSpecial
Cells(StartRow, 1).Select
Range(Cells(StartRow, 1), Cells((StartRow + UserValue - 1), 1)).Value = ""
Range(Cells(StartRow, 2), Cells((StartRow + UserValue - 1), 2)).Value = ""
Range(Cells(StartRow, 3), Cells((StartRow + UserValue - 1), 3)).Value = ""
Range(Cells(StartRow, 6), Cells((StartRow + UserValue - 1), 6)).Value = ""
Range(Cells(StartRow, 7), Cells((StartRow + UserValue - 1), 7)).Value = ""
Range(Cells(StartRow, 9), Cells((StartRow + UserValue - 1), 9)).Value = ""
Range(Cells(StartRow, 10), Cells((StartRow + UserValue - 1), 10)).Value = ""
Range(Cells(StartRow, 11), Cells((StartRow + UserValue - 1), 11)).Value = ""
Range(Cells(StartRow, 12), Cells((StartRow + UserValue - 1), 12)).Value = ""
Range(Cells(StartRow, 13), Cells((StartRow + UserValue - 1), 13)).Value = ""
Range(Cells(StartRow, 14), Cells((StartRow + UserValue - 1), 14)).Value = ""
Range(Cells(StartRow, 15), Cells((StartRow + UserValue - 1), 15)).Value = ""
Range(Cells(StartRow, 16), Cells((StartRow + UserValue - 1), 16)).Value = ""
Range(Cells(StartRow, 17), Cells((StartRow + UserValue - 1), 17)).Value = ""
Range(Cells(StartRow, 18), Cells((StartRow + UserValue - 1), 18)).Value = ""
Range(Cells(StartRow, 19), Cells((StartRow + UserValue - 1), 19)).Value = ""

End Sub
Customer: replied 3 years ago.
It gets through the Input Boxes, then gives a error 400?
Expert:  JamesStone replied 3 years ago.
let me know which version of excel you are using. I'm not having a problem with this using a template that includes values and formats through columns A - Z
Expert:  JamesStone replied 3 years ago.
Alright, give this a try. For some reason when using MS Excel 2000 the selection.paste works, but causes an error in 2007.

If I use just "paste" in 2007 it works.


Sub RowShift()
Dim StartRow As Integer
Dim UserValue As Integer
Dim X As Integer
X = 0
FullValue = 0

StartRow = InputBox("Which row do you want to start on?")
UserValue = InputBox("How many lines do you want to insert?")

Rows(StartRow).Select

Do Until X = UserValue
Selection.Rows.Insert (xlShiftDown)
X = X + 1
Loop

Rows(StartRow + UserValue).Select
Selection.Copy
Rows(StartRow & ":" & (StartRow + UserValue)).Select
Paste
Cells(StartRow, 1).Select
Range(Cells(StartRow, 1), Cells((StartRow + UserValue - 1), 1)).Value = ""
Range(Cells(StartRow, 2), Cells((StartRow + UserValue - 1), 2)).Value = ""
Range(Cells(StartRow, 3), Cells((StartRow + UserValue - 1), 3)).Value = ""
Range(Cells(StartRow, 6), Cells((StartRow + UserValue - 1), 6)).Value = ""
Range(Cells(StartRow, 7), Cells((StartRow + UserValue - 1), 7)).Value = ""
Range(Cells(StartRow, 9), Cells((StartRow + UserValue - 1), 9)).Value = ""
Range(Cells(StartRow, 10), Cells((StartRow + UserValue - 1), 10)).Value = ""
Range(Cells(StartRow, 11), Cells((StartRow + UserValue - 1), 11)).Value = ""
Range(Cells(StartRow, 12), Cells((StartRow + UserValue - 1), 12)).Value = ""
Range(Cells(StartRow, 13), Cells((StartRow + UserValue - 1), 13)).Value = ""
Range(Cells(StartRow, 14), Cells((StartRow + UserValue - 1), 14)).Value = ""
Range(Cells(StartRow, 15), Cells((StartRow + UserValue - 1), 15)).Value = ""
Range(Cells(StartRow, 16), Cells((StartRow + UserValue - 1), 16)).Value = ""
Range(Cells(StartRow, 17), Cells((StartRow + UserValue - 1), 17)).Value = ""
Range(Cells(StartRow, 18), Cells((StartRow + UserValue - 1), 18)).Value = ""
Range(Cells(StartRow, 19), Cells((StartRow + UserValue - 1), 19)).Value = ""

End Sub
Customer: replied 3 years ago.
Now, when I run it faults out and highlights the Paste text in the macro. I'm using 2010, does that make a difference?
Expert:  JamesStone replied 3 years ago.
Hmm I don't have access to 2010, that makes sense why there could be a conflict there. Essentially we've just got to figure out the correct syntax for the "paste" line.

It should be easy to pinpoint the section of code where this issue is occurring IE the rows are inserted, the model row is copied, the range where it would paste is selected...this is where I was seeing the error between 2000 and 2007, so that appears to be where the holdup is.

Is this the same for you?
Expert:  JamesStone replied 3 years ago.
Apparently excel 2010 introduced some "improvements" to the paste function.

I did some research and you'd want to replace the paste line with:

SeriesCollection.Paste (instead of Selection.Paste, or "Paste")

Since I don't have access to excel 2010, you'll have to check that for me.

If that doesn't solve it, it may be easiest if you were to run the macro up until the point it fails out, then "record" a macro, copy the row and paste it over the range and then "stop" the macro. Then if you go into the VBA there should be a recording of the corresponding code.

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
< Last | Next >
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
  • He answered my question promptly and gave me accurate, detailed information. If all of your experts are half as good, you have a great thing going here. Diane Dallas, TX
 
 
 

Meet The Experts:

 
 
 
  • Ron

    ASE Certified Technician

    Satisfied Customers:

    21779
    23 years with Ford specializing in drivability and electrical and AC. Ford certs and ASE Certs
< Last | Next >
  • http://ww2.justanswer.com/uploads/FO/fordguy4u/2011-12-17_222940_HPIM1257.64x64.JPG Ron's Avatar

    Ron

    ASE Certified Technician

    Satisfied Customers:

    21779
    23 years with Ford specializing in drivability and electrical and AC. Ford certs and ASE Certs
  • http://ww2.justanswer.com/uploads/lyeung1/2010-07-25_032152_tn_IMG_0241.JPG Dr. Y.'s Avatar

    Dr. Y.

    Urologist

    Satisfied Customers:

    18785
    I am fellowship trained specializing in general urology and reconstructive urology.
  • http://ww2.justanswer.com/uploads/docjohn174/2008-12-13_170143_johnask.jpg John's Avatar

    John

    Home Appliance Technician

    Satisfied Customers:

    13453
    Appliance repair business owner for over 43 years.
  • http://ww2.justanswer.com/uploads/MU/multistatelaw/2011-11-27_173951_Tinaglamourshotworkglow102011.64x64.jpg Tina's Avatar

    Tina

    Lawyer

    Satisfied Customers:

    8681
    JD, BBA Over 25 years legal and business experience.
  • http://ww2.justanswer.com/uploads/dermdoc19/2010-09-30_160749_Photo_122807_015.JPG dermdoc19's Avatar

    dermdoc19

    Dermatologist

    Satisfied Customers:

    3948
    30 years practice in general and cosmetic dermatology
  • http://ww2.justanswer.com/uploads/BI/birddoctor/2012-6-22_173214_birddoctor.64x64.png Dr. Pat's Avatar

    Dr. Pat

    Bird Veterinarian

    Satisfied Customers:

    3463
    25+ years working primarily or exclusively with birds
  • http://ww2.justanswer.com/uploads/RY/rydergar/2012-6-6_192240_IMG0328.64x64.JPG Dr. Gary's Avatar

    Dr. Gary

    Cat Veterinarian

    Satisfied Customers:

    3422
    DVM, Emergency Veterinarian, BS (Physiology)
 
 
 

Related General Questions

Chat Now With An Expert
JamesStone
JamesStone
Data Analyst
42 Satisfied Customers
BA English Teaching, Data Analyst 7 Years