Insert Rows Based on Consecutive Qulaifiers


Part 1 of a bigger project - The attachment has three tabs and the E ID and EE is fictitious for obvious reasons. The data represents when an employee has change thier time-card.  The code is already in VBA consisting of two macros.  How do I combine them so it runs as one?

The goal is for every Consecutive CC From listed per E ID and EXP_ITEM_DATE that a row be inserted and vice versa for CC To(s).

(This will enable me to copy paste data/Part 2 Paste Row Data Based On Several Qualifiers.)

Example: Flinstone has two time cards he changed.  4/10 and 4/11If you look at 4/10 what is he missing? He has one CC From that offsets a CC To for that date but he also has two other CC To(s) missing CC From(s).  So, I would need to insert a blank row after line 3, and 4.

If you look at 4/11 what is he missing? He has two CC From(s) that offsets one CC To for that date. He is missing a CC To to offset the other CC From.  So, I would need to insert a blank row after line 6.

I am pretty confident that the code works if you run each macro separately but if there is a more efficient way to do it I am all ears.

Thank you - Cora



Your question has the following inaccuracies and  inconsistencies:- (1) You state, "The goal is for every Consecutive CC From listed per E ID and EXP_ITEM_DATE that a row be inserted." But in your workbook you have this formula, which ignores EXP_ITM_DATE, =IF(B4<>B5,"Last Employee",""). (2) What is the purpose of column L? Is it to explain what you want done by the code? Or does it have some other purpose? This is important because the formulas in column L will be upset by row insertion and may have to be repaired. (3) What is the significance of the highlighted rows in your sample data? I was thinking of highlighting the inserted rows but your sample data suggests that highlights already exist in your data and inserted rows should inherit the highlight of adjacent data.
(4) Can you assure that the first row of your data will definitely have the designation "CC From"? Please modify your question and/or the attachment in reference to each of these 4 points.
Variatus (rep: 2454) Sep 27, '18 at 9:29 pm
Hello, and thank you very much for your response.  My goal is correct.  1.  The spreadsheet is already sorted by E ID, EXP_ITEM_DATE, Project Code, before the inserted row macro event takes place which is why the formula only targets the consecutive CC From/CC To. 2. There is no purpose for column L other than to show the helper of this project where one starts and one ends.  So, no repairing will be needed. 3.  The highlighted transactions are simply cosmetics to assist the helper in understanding the project.  The highlighting of rows inserted is an excellent idea and would work. 4.  No, I can’t definitively say that the transactions will begin with a CC From. I hope this addresses your concerns. Mil Gracias, Cora
CoraG (rep: 4) Oct 2, '18 at 10:52 am
Add to Discussion


Selected Answer

The attached workbook has code which will insert missing rows. All the code is in the Module TeachExcel. First, familiarise yourself with the layout of the workbook.

  1. Tabs Sampled Data and Instructions Part 1 are untouched from what you published. Neither sheet is referenced by the code.
  2. Tab Modified Sample is a copy of Sampled Data in which the yellow highlight was removed. The purpose of this sheet is to have an "original" of the sheet on which the code is tested.
  3. Tab Solution is used for testing. The code will modify this sheet. To repeat the test copy Modified Sample to Solution. You should also copy your real data to Solution and run tests.

At the top of the code you will find the enumeration Nws which permits you to define another first data row and change all of the referenced columns. Read the instructions preceding and within the Enum.

The procedure to run is Sub InsertMissignData. You can change the name of the worksheet on which to insert rows in this line of code. 

Set Ws = ThisWorkbook.Worksheets("Solution")

You can run the code repeatedly on the same data set without ill effects. You can also modify the (test) data after running the code and run it again. The procedure will call the sub CopyData which is in preparation of the next step and as yet blank. Before we can get there this step of the project must be tested and approved (by you).

For the moment, the function InsertMissingRows does all the work. You may notice that it is unchanged since Sept 28, as is the rest of the project. The progress we made since then is that you have declared column Last Employee irrelevant. That enables ignoring the fact that its formula is wrong. It doesn't prove that my code is correct. This can only be ascertained by you, testing real data. Look for this kind of combination.

  • EmployeeA       From
  • EmployeeB       To

The From/To sequence is perfect. Your existing code will not insert a row. In fact, however, two rows are missing. My code will find this omission and insert two rows, provided that it can differentiate between EmployeeA and EmployeeB. You have offered several methods of how to do that and still haven't clarified which one is correct. Therefore my code allows you to modify it to suit your final requirements. Look for the function SameId. Look for these lines of code.

    ' include the columns of cells which must be identical in the array
    '   Eligible values:-
    '   NwsID, NwsAccount, NwsSubAcc, NwsProject, NwsTask, NwsItmDate, NwsEndDate
    Clms = Array(NwsID, NwsItmDate)

The function will judge the data in two adjacent rows as belonging to the same employee if the cells in columns NwsID and NwsITMDate are identical. This is controlled by the code line Clms = Array(NwsID, NwsItmDate).

According to one of your earlier explanations, supported by the highlights in your Sampled Data, this isn't enough. You have indicated, and later reversed, that the account number should also be the same. The account number can be included in this function's tests by adding it to the array thus, Clms = Array(NwsID, NwsItmDate, NwsAccount). The sequence of the numbers in the array is immaterial as is the number of elements. I have added a complete list of all eligible columns in the remark above the array's specification, to wit, NwsID, NwsAccount, NwsSubAcc, NwsProject, NwsTask, NwsItmDate, NwsEndDate.

The effect of this function will be even greater when we proceed to copying data. Perhaps the specification for inserting rows and copying data isn't the same. I look forward to seeing your question. For now, we can't proceed to the next step until the inserting is perfect. When you test the code on real data, don't worry too much about the rows which are inserted. Even your previous code - much simpler than mine - could do the From/To sequence almost correctly. One of the tests you should run is to use your old code on the Solutions tab and then run InsertMissingData on the result. The rows inserted by my code are those your earlier code missed. Examine them. Perhaps they should have been missed :-)

Make it your job to find the rows which are not inserted. If the start and end of any employee's data is not defined correctly the result will not be extra rows which are not required. The mistake will be in required rows which were not inserted. That is much harder to find but absolutely necessary if you want to account for employee time correctly.

Once you are sure that the code resulting from this thread is correct, post the workbook I now attach (or its successors as we evaluate your test results) with tabs 1, 3 and 4 deleted, perhaps a worksheet Instructions Part 2 added, code Module1 removed and explain what you need done. Your already existing post for the next step has a different base and doesn't implement what you will have learned from testing the attached code. Mildly put, it isn't so useful.

BTW, the 20K+ rows of your data will take some time to process. Therefore I have added a progress indicator to reassure you that your PC is still working. Look at the status bar at the bottom left of your screen while the code is executing.



Hi and thank you.  The code works perfect in both the sample and actual data.  The only thing to keep in mind is that copy/paste (Part 2) will need to be copy/paste/special so we don't lose the highlighting.  Looking at the code you created I was having a hard time understanding the flow of it but I think I understand it now.  In any case, I have renamed and attached the file and it is located in the Part 2 thread ending with 100918 as the date. I look forward to your seeing your response in the other thread. Mil Gracias! Cora
CoraG (rep: 4) Oct 11, '18 at 10:41 am
Funny. I didn't receive a notification of modification of your other thead. Perhaps that is because you didn't change the text. I suggest you review the text because it is probably out-dated. Note that any suggestions you post here will not be available for consideration in any other thread.
Variatus (rep: 2454) Oct 12, '18 at 4:04 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login