Add Row With Macro

**doubleuson** · 08-06-2018, 06:30 AM

Hi Excel pros. I'm having a load of trouble with my basic macros.

I have two buttons on a database whose code looks like this:

Screen Shot 2018-08-06 at 11.23.39.png

One for adding a row, one for deleting a row. The 'delete row' button works as it should.

The 'add row' button, however, seems to struggle: When pressed, it adds a row before the last row when I want it to add a row after the last row. Additionally, I want it to create a row with a formula in the A column to increase the number by one:

Screen Shot 2018-08-06 at 11.24.33.png

Could someone suggest edits for my code to achieve this?

Thanks in advance.

**MarMo** · 08-06-2018, 09:23 AM

you could try :

Please Login or Register  to view this content.

i've added a small WB with the code.

**Vraag en antwoord** · 08-06-2018, 09:25 AM

or

Please Login or Register  to view this content.

**romperstomper** · 08-06-2018, 09:35 AM

Just FYI, Shift:=x2Down should be Shift:=xlDown. (note that is a lower case L and not the number 1)

**doubleuson** · 08-06-2018, 10:06 AM

Originally Posted by MarMo

you could try :

Please Login or Register  to view this content.

i've added a small WB with the code.

I implemented a slightly adjusted (changing cell E to cell X) version of this code to my database and I got this error:

Screen Shot 2018-08-06 at 15.02.44.png

I, also, cannot open your WB due to some sort of unsupported Excel features. So I attached a copy of my workbook below, could you possibly reply with the code I can exactly copy and paste into the macro box for that button?

Additionally, I need the new row to expand the active table, else the formulae on the other sheets will not adjust their range to the additional variables.

Thanks.

**Vraag en antwoord** · 08-06-2018, 10:20 AM

There are more reactions

**doubleuson** · 08-06-2018, 11:38 AM

Originally Posted by Vraag en antwoord

There are more reactions

Hey Vraag, I entered your macro code into VBA and it came up with this error.

Screen Shot 2018-08-06 at 16.37.19.png

**Vraag en antwoord** · 08-06-2018, 12:01 PM

The file does not match with the pictures. In which sheet can we search?

Place a representative file and show how you applied the code

**Neilesh Kumar** · 08-06-2018, 12:11 PM

Hi doubleuson, Please find here attached the solution. I hope you are looking exactly the same.

If the provided solution matches with your requirement then kindly mark thread as solved.

Add Blank Row after last row.xlsb

**Keebellah** · 08-06-2018, 04:53 PM

Hi doubleuson, I'm attaching two links that helped me when I was struggling with tables and named ranges.
You probably won't have the time seen your Friday deadline but I can certainly advice is especially since your add and remove macros were from from correct
https://www.thespreadsheetguru.com/b...t-excel-tables
https://www.thespreadsheetguru.com/b...o-named-ranges

Did you read the previous post with the macro code I posted for the updating of the formulas?

**doubleuson** · 08-07-2018, 04:55 AM

Originally Posted by Neilesh Kumar

Hi doubleuson, Please find here attached the solution. I hope you are looking exactly the same.

If the provided solution matches with your requirement then kindly mark thread as solved.

Attachment 585017

Unfortunately, this formula simply crashes the spreadsheet. Additionally, were it to work, does it apply a new number in the A column which will be the previous A cell +1?

**Neilesh Kumar** · 08-07-2018, 05:59 AM

Hi doubleuson, It works quite fine at my end. Make sure macro settings are enabled. Please find here attached with the upgraded VBA where with the adding row the script will also provide the serial number in Column A.

If the provided solution matches with your requirement then kindly mark thread as solved.

Add Blank Row after last row.xlsb

**doubleuson** · 08-07-2018, 06:08 AM

The file you sent works, however, my attempts to import it into my current database do not. Here is my error message:

Screen Shot 2018-08-07 at 11.07.32.png

**romperstomper** · 08-07-2018, 06:14 AM

I think you forgot to copy the SerialCode routine from module2 into your current workbook.

**Neilesh Kumar** · 08-07-2018, 06:25 AM

Hi doubleuson, In the attached sample workbook i have added two modules. So please insert another module and paste over there the script. It will certainly work.

**doubleuson** · 08-07-2018, 06:31 AM

Originally Posted by rorya

I think you forgot to copy the SerialCode routine from module2 into your current workbook.

I did, but having done so and created a Module2 within my database, it proceeded to crash the spreadsheet altogether.

If I was better than slightly okay at doing this, we wouldn't be here...

**Neilesh Kumar** · 08-07-2018, 06:42 AM

Hi doubleuson, please find here attached the revised one where i have removed the module 2 and copied the module 2 script in module 1. I really surprised that it proceeded to crash where it works perfectly in my case.

Please try the revised one and do let me know if it also proceeds to crash.

Add Blank Row after last row.xlsb

**AliGW** · 08-07-2018, 06:43 AM

@ Doubleuson

It's not that you're not OK at it, it's just that you have taken on so much new learning in a short space of time. I think you over-promised a bit at work and are now in danger of under-delivering.

To help things along here, you ought to attach your workbook. It will be easier to troubleshoot seeing what you have done in situ.

**Neilesh Kumar** · 08-07-2018, 07:03 AM

Originally Posted by AliGW

It's not that you're not OK at it, it's just that you have taken on so much new learning in a short space of time. I think you over-promised a bit at work and are now in danger of under-delivering.

To help things along here, you ought to attach your workbook. It will be easier to troubleshoot seeing what you have done in situ.

I do apologies Dear Expert. I will certainly take care henceforward.

**doubleuson** · 08-07-2018, 07:14 AM

Originally Posted by Neilesh Kumar

I do apologies Dear Expert. I will certainly take care henceforward.

I believe she is addressing me. You're doing well, I'm sure.

Originally Posted by AliGW

It's not that you're not OK at it, it's just that you have taken on so much new learning in a short space of time. I think you over-promised a bit at work and are now in danger of under-delivering.

To help things along here, you ought to attach your workbook. It will be easier to troubleshoot seeing what you have done in situ.

You are quite correct in that. I did not realise how difficult to implement something that seemed so intuitive was. I have no idea about pricing for projects.

I thought I had attached my workbook earlier, but I have now, regardless.

How much would you reckon it would cost to finish off my project?

**AliGW** · 08-07-2018, 07:16 AM

Originally Posted by Neilesh Kumar

I do apologies Dear Expert. I will certainly take care henceforward.

Not aimed at you at all - sorry, should have made it clear.

**doubleuson** · 08-07-2018, 07:17 AM

Originally Posted by Neilesh Kumar

Hi doubleuson, please find here attached the revised one where i have removed the module 2 and copied the module 2 script in module 1. I really surprised that it proceeded to crash where it works perfectly in my case.

Please try the revised one and do let me know if it also proceeds to crash.

Attachment 585126

So this is my code, and it either insists SubAddRow() is not defined; or it crashes the entire document. I'm sure a small tweak will implement it.

Screen Shot 2018-08-07 at 12.15.38.png

Please see the post above for the workbook. If you could make it work in there, I would be greatly pleased.

Copying your most recent code inclusive of the SerialCode causes the spreadsheet to crash. Can you try implementing it directly in my workbook and seeing what effect that has?

**AliGW** · 08-07-2018, 07:19 AM

How much would you reckon it would cost to finish off my project?

How much is it worth to you?

I don't know, to be honest. There are at least three strands to it at the moment, and I'm not sure that it's entirely clear in any of them how much more there is to be done beyond what various helpers here are working on at the moment. As you have pretty full engagement at the moment, I would just dig in and see if you can get it done on a lot of goodwill.

I would just counsel your not flying by the seat of your pants like this again.

**doubleuson** · 08-07-2018, 07:28 AM

I mean if the person with the most understanding of my requirements (which I do agree I have not been able to put into words so well), which is most likely Keeballah, wants to step up and implement the final work for a reasonable sum, I can go through the points system. I'm currently struggling to figure out how and where to implement the two macros and functions he/she wrote for me.

**AliGW** · 08-07-2018, 07:47 AM

I believe Keebellah said in one of your threads that they would be away for a couple of days now.

**doubleuson** · 08-07-2018, 07:55 AM

You're right. I just recall that now.

It doesn't, to me, seem like what needs to be done is hugely difficult, so long as you understand what you are doing. How would I go about getting a quote for the remainder of the work?

**Neilesh Kumar** · 08-07-2018, 08:07 AM

Hi doubleuson, Please try the attached. I feel i have fixed the crash in the revised one. Please do let me know if it further proceeds for crash.

Request to you kindly do mark as Solved thread if it matches with your requirement.

Proposed DA Learner Details (Hyperlink Edit).xlsb

**doubleuson** · 08-07-2018, 08:14 AM

Yep, that's resolved that issue. Thank you very much.

/SOLVED

Still have open issues, one significantly more complex than the others:

https://www.excelforum.com/excel-for...ssistance.html

https://www.excelforum.com/excel-for...le-sheets.html

https://www.excelforum.com/excel-for...-criteria.html

**Neilesh Kumar** · 08-07-2018, 08:17 AM

Glad to help Dear. Yeah certainly i would look into those since i am at work so probably by today night i would try to resolve them if i could able to do so.

**doubleuson** · 08-07-2018, 08:19 AM

I would very much appreciate the help, if you are proficient with VBA coding. Message me if you need any further clarification.

**Keebellah** · 08-08-2018, 01:53 AM

I’m indeed away but curiosity ... I can’t do much on my phone but will take a look tonight (Wednesday) after i’m home, no promises but hope i can help along

**Keebellah** · 08-08-2018, 06:43 AM

Can you attach the most recent version of your file (again) if possible?
So when I get home later this evening I at least know that that is your latest version.
Your deadline is a serious issue for you but like I said I cannot guarantee anything except helping along where I can, and BTW I'm a he

You also read @AliGW's and my comment of the other post.

**AliGW** · 08-08-2018, 06:45 AM

I think the latest version of the workbook is being used here: https://www.excelforum.com/excel-for...le-sheets.html

**doubleuson** · 08-08-2018, 07:45 AM

I spoke to my boss and told them that I doubt I can get it all done by then. They'd rather it gets done well than get it done immediately, since it won't be used until the end of August anyway.

I attached the newest version of the file to the post I made in the other thread we have been communicating on.

I appreciate all and every bit of help provided, and will work with you at your convenience since, after all, this is non-commercial work.

**AliGW** · 08-08-2018, 07:50 AM

I spoke to my boss and told them that I doubt I can get it all done by then.

This made me snigger. I think you will need to include a credits tab in your workbook.

Thank goodness the pressure's off for now!!! Lucky reprieve there.

**doubleuson** · 08-08-2018, 08:00 AM

Well I can't exactly admit I can't do it and tell them I've given it to someone else to do.

A nice learning curve for a research intern.

I am, however, more than willing to credit contributors in the workbook.

**AliGW** · 08-08-2018, 08:10 AM

I think you MUST credit them! And not just because it would be dishonest not to: in practical terms, when and if something goes wrong or needs changing, or someone at work asks you how it works, you are going to have to admit you don't know.

**doubleuson** · 08-08-2018, 08:16 AM

I intend to, but how they choose to be credited is up to them (by fullname, username, etc). Especially with the macros, I have no issue with a dummy row being created into the code with an individual's credit/signature.

With regards to diagnosing issues with it, well, I intend to study it in-depth (rather than just present it to them) to see how it works, however, chances are I'll be back on here when it has a problem.

**Keebellah** · 08-08-2018, 08:22 AM

Good to hear the pressure’s off a bit, i’ll Do my best and try also to include sufficient comments for you to be able to pickup on the vba.

**AliGW** · 08-08-2018, 08:23 AM

I have to tell you, this is keeping me greatly amused (I'm not hard to please)!!!

I have had help with a few snippets of VBA in the past, but even having studied them, I'm still no nearer understanding how they work. I think your chances of fully understanding the code that you've been gifted here by the end of August are fairly slim. I think you're being just a tad over-optimistic ... again!

With the best will in the world, I think the chances of your being back here are pretty much 100%. Not that I think you aren't capable of learning it all - far from it - it's just going to take longer than a couple of weeks, that's all.

I do hope your boss is pleased with it, anyway.

**doubleuson** · 08-08-2018, 08:27 AM

@Keebellah: Has anyone compared you with a Patron Saint of late?

@AliGW: Yeah I have to be optimistic that everything will pan out, else I'd have to contemplate a change in profession.

**AliGW** · 08-08-2018, 08:32 AM

LOL! LOL! and ROFL!!!!

Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Re: Add Row With Macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula SUM(A+B) - Copy formula to SUM(D+E), SKIP C. Automatic formula

Bookmarks

Bookmarks

Posting Permissions