Help combining macros to delete rows on conditions and add data into cells on conditions

**JayJayGC** · 10-03-2017, 04:02 AM

Hi Everyone,

I have 2 really great macros I adapted to my needs from other posts but really want to combine them into one macro so that users only have to use one button to complete both processes. One compares column data between sheets and adds to the next empty row in sheet 1 if data from sheet 2 is not present. The other deletes rows from sheet 1 if data exists there that does not exist in sheet 2. This is basically an update to data that will be completed every month when new raw data is pasted into sheet 2. The reason I want to combine them is to make it a one button process for our end users.

Below is my code from both Subs - I do know that the references to sheets and ranges will need to change but I can't seem to work out which to do first and which references can be changed and which can't.

Any help would be very much appreciated!

This is the deleterows sub:

Please Login or Register  to view this content.

And the UpdateAddData sub is below:

Please Login or Register  to view this content.

Thanks in advance!

**Richard Buttrey** · 10-03-2017, 04:45 AM

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

Rather than interpreting your code and trying to work out what it's intended to do we generally like to know what the overall aim of the task is so that we can work from first principles. Your macros may not be the most efficient or elegant solutions.

For instance you're using a looping macro to delete rows. You should always avoid loops for this sort of thing if possible since loops are inherently slow, particularly when they have to jump back and forth between VBA and the Excel App - there's a large time overhead there. The fastest way I know of doing that sort of stuff is to use an Autofilter to display the rows you want to delete and then use a .SpecialCells(xlCellTypeVisible) syntax to delete the whol block of rows with one instruction.

Neither is it clear from your macro or description whether there is any precedence to which macro should run first and whether there are any constraints. An overall statement of what the system is expected to do and how it's used would help.

**JayJayGC** · 10-03-2017, 06:52 AM

Hi Richard,

Thank you for your response. I have attached both a sample excel file, with reduced data for confidentiality, and a word doc that contains before and after screenshots of the excel data.

There is no constraint around which macro runs first as they do almost opposing things and the results of one will not affect the running or results of the other.

Basically what I want the macro to do is to check whether a value (in this case a first and last name) in column A of sheet 2 (7807 Data) exists in column A of sheet 1 (7807) and if not add the value of the missing column A data in sheet 2 to the first empty cell in Column A on sheet 1 (under existing data without changing or moving anything above it). Secondly, I need to check whether a value (again the first and last name) in column A of sheet 1 (7807) exists in column A of sheet 2 (7807 Data) and if not delete the entire row from sheet 1. Basically I want to have deleted staff that don't appear in the most recent raw data paste into sheet 2 be removed from the master display list in sheet 1 and any new staff be added to the raw data paste into sheet 2 to the bottom of the existing staff data in sheet 1. I hope that makes sense!

Raw data is pasted into sheet 2 (7807 Data) on a regular basis (say weekly). I then have INDEX/MATCH functions in use on sheet 1 (7807) that display the contents of the columns/rows that correspond to Column A in sheet 1 (based on first name/last name).

I sure hope I've covered everything... and I thank you for your assistance!

**sintek** · 10-03-2017, 08:07 AM

Hi JayJayGC

Let me see if I understand your requirement...
One code to do following:
1.Check Sheets("7807 Data") and if info exists there but not on Sheets("7807") then to add to Sheets("7807")
2. Check Sheets("7807") and if info exists there but not on Sheets("7807 data") then to delete from Sheets("7807")

If so....

Please Login or Register  to view this content.

**JayJayGC** · 10-03-2017, 08:28 AM

Hi again Sintek, and thank you! After yesterday I got up the courage to try some other things with my sheets

Yes that's precisely what I'm after... but I'm getting a runtime error on the delete portion - its stops here:

Please Login or Register  to view this content.

Actually this could be because I've protected the sheet... yes? I do have an "OpenWorksheet" unprotect macro in place for "UserInterfaceOnly:=True" but that won't allow the delete will it? ... Hmmm... I'll unprotect it and see if it works before I try to tackle the protect problem again

**JayJayGC** · 10-03-2017, 08:33 AM

Hi again, so it's nice to see I'm not a complete idiot - at least I realised what the problem was... it worked perfectly once I unprotected the sheet - you're an absolute legend sintek!!

But how do I protect all the cells that have my INDEX/MATCH data and still allow a delete row? Can I unprotect inside the macro and then protect again at the end? I'm sure I've read that's tricky and dangerous... any pointers you can give me would be fantastic - again

**sintek** · 10-03-2017, 08:41 AM

See amended code in Post 4
Change "Your Password" to the password you are using...

**Richard Buttrey** · 10-03-2017, 08:59 AM

Hi,

Maybe something like the following (also see attached)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**JayJayGC** · 10-03-2017, 08:59 AM

Thank you! It works perfectly - I'm indebted to you again

Many thanks for helping me finish this project - it's been a tough week trying to get my amateur attempts at coding to work, and I would never have got there without your help. I learnt a lot from you, I appreciate that most of all.

**sintek** · 10-03-2017, 09:14 AM

@ Richard

Absolutely love your code, so much more efficient.
One question though...
Should This line of code

Please Login or Register  to view this content.

perhaps be

Please Login or Register  to view this content.

**JayJayGC** · 10-03-2017, 09:50 AM

Gosh Richard, thank you too! I'm quite proud that I understand (more or less) what your code is doing and why... last week I would have struggled with that

Yours is definitely more efficient, sintek is quite correct on that - so thanks again, I appreciate the help very much (particularly since I've now learnt something else, winning!)

You both deserve medals for helping newbies learn

**Richard Buttrey** · 10-03-2017, 09:56 AM

Mmm
Interesting Q.

I suppose it all depends on whether there is a row of column labels that will always be present.

Must admit I've always felt the CurrentRegion property could be much enhanced by having a parameter that indicated whether column labels should be excluded. I often find myself working round this, particularly when an autofilter doesn't return any values making it a nuisance to bypass any code that needs to do stuff if there are filtered values.

**sintek** · 10-03-2017, 10:14 AM

Points to ponder...
I always just use...

Please Login or Register  to view this content.

Help combining macros to delete rows on conditions and add data into cells on conditions

LinkBack

Thread Tools

Rate This Thread

Display

Help combining macros to delete rows on conditions and add data into cells on conditions

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Re: Help combining macros to delete rows on conditions and add data into cells on conditio

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Delete rows with conditions

[SOLVED] Delete rows with conditions

Retrieve data from table - multiple conditions / conditions within conditions

[SOLVED] Trying to delete some rows if conditions met or do nothing if not met

Combining Cells for specific conditions

Delete rows with conditions

Delete Rows & Column Per Conditions

Bookmarks

Bookmarks

Posting Permissions