Add New Row by clicking a Button using VBA

**pchugh** · 05-29-2018, 12:37 AM

Hi Everyone,

I have the below code to insert new rows in my worksheet. This code is working fine but I need some modifications in this. I need rows to be inserted not before the last row in sheet but when it finds "Last Row" in column C, it should insert row above it. Below this "Last Row", I do have some formulas that calculates the total value from the above rows.
This code currently finds the last row in sheet and adds a new row and copy down the formulas from cell C18:DU18 which is correct.
Can you help me to modify this code so that it only adds row above "Last Row" and still copies down formula from C18:DU18

Please Login or Register  to view this content.

**pchugh** · 05-29-2018, 01:28 AM

Also I just realised that this VBA is also copying the contents along with the formula.. Can the VBA just copy the formula and not contents.

**alansidman** · 05-29-2018, 05:11 AM

Perhaps this:

Please Login or Register  to view this content.

**pchugh** · 05-29-2018, 07:03 PM

Hi Alan,

The code u provided is failing on the below line:
Range("C" & lr).PasteSpecial xlPasteFormulas
I guess something wrong here which I cant figure out as I am new to VBA thing.
Also I need this code to insert a new row above when it finds "Last Row" in column C. This code just enters a new row at the last line which i DONT need. is this also achievable in this code.

**alansidman** · 05-29-2018, 07:38 PM

try this. Make changes to suit your particular worksheet as noted.

Please Login or Register  to view this content.

**pchugh** · 05-29-2018, 08:10 PM

This code is working but again its copying the contents as well along with the formula's in the new rows.
Also this code is adding row above the last row but I want this code to find "Last Row" text in column C and add new rows above it. I have typed "Last Row" in the last row in column C so that any new rows can be inserted above it. Is it possible?

**alansidman** · 05-29-2018, 08:26 PM

Since we are going around on this, it is time to provide a sample work book showing a before and after scenario. You will need to mock up manually the after scenario.

If you have confidential data in your workbook, dummy it up.

**pchugh** · 05-29-2018, 08:36 PM

I have attached a dummy file with before and after. I need rows to be added above Last Row text string in column C. And the new rows should copy the formulas only from column C18 to DU18 in my sheet. The row after the text string Last Row has got totals from the above.

**alansidman** · 05-29-2018, 09:03 PM

This should take care of the insertion correctly. My apologies in understanding what your last row was. Since there is no data in your cells to copy, I am not sure what is happening there. Do you have calculations set to manual?

Please Login or Register  to view this content.

**pchugh** · 05-29-2018, 09:10 PM

Now its adding row where i wanted but again its just copying the data from the above rows C18 to DU18. Its not copying formulas down in each row. I have formulas in each row as drop down or as look up function in some rows.

**alansidman** · 05-29-2018, 09:27 PM

I am not understanding what is the issue with the copying. I suspect it has to do with the comboboxes as you indicated above. Since you have not shown what is in Row 18 or its layout, there is nothing I can offer you. You are asking me to solve a problem I cannot see. It is much like being turned around a couple of times blindfolded and then asked to hit the bulls eye throwing darts. Time for bed. I will check back tomorrow.

**pchugh** · 05-29-2018, 11:06 PM

okay I just added one line in your code and its working partially for me. I just added the below line in the code:
Range("C" & lr).ClearContents
below is complete code.
Its now clearing contents from column C and retaining formula and formatting but rest of the columns still have the contents and formula both. How can this command be extended in the code to clear contents of the new added row in all the columns until DU and retain the formula and formatting and NOT contents.

Please Login or Register  to view this content.

**alansidman** · 05-30-2018, 08:45 AM

If I am understanding your request correctly, which I am not sure I do, then the following code should do what you are asking.

Please Login or Register  to view this content.

**pchugh** · 05-30-2018, 09:14 PM

Thank you Alan. I guess I have achieved what I was intending to. Thank you again.

**alansidman** · 05-30-2018, 10:01 PM

you are welcome and thanks for the rep

Add New Row by clicking a Button using VBA

LinkBack

Thread Tools

Rate This Thread

Display

Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Re: Add New Row by clicking a Button using VBA

Thread Information

Users Browsing this Thread

Similar Threads

Clicking a button on Web using VBA

VBA button to copy values from one range to another by clicking the button

#How Clicking the button with Id and Name

clicking website button

VBA/IE button clicking issue

[SOLVED] clicking a button on a promt box VBA

[SOLVED] How do I run the macro for a button click without clicking the button?

Bookmarks

Bookmarks

Posting Permissions