insert rows on data change

April 22, 2011 at 06:34:11
Specs: Windows XP

i've process names listed in column I and each process name listed should be 63 times repeated. Some process names are less than 63 times repeated.

Could anyone assist me with a code that would insert the remaining rows i'e count number of process name in col I when process name changes, if less than 63 insert the remaining rows.

Many thanks,

See More: insert rows on data change

April 22, 2011 at 17:45:37
The following code assume that the first "Process" is in Row 2.

I strongly suggest that you test this code in a backup copy of your workbook since macros can not be easily undone.

Sub SixtyThree()
'Initiate Row and Process Counters
  rw = 1
  procCount = 1
'Start of Process Count
  addCount = 0
  rw = rw + 1
'Loop to Count Processes
  If Cells(rw, "I") = Cells(rw + 1, "I") Then
   If Cells(rw, "I") = "" Then Exit Sub
     procCount = procCount + 1
     rw = rw + 1
     GoTo ProcessCounter
   End If
'Insert Additional Rows As Needed
   If procCount < 63 Then
     addCount = 63 - procCount - 1
     Cells(rw, "I").Copy
     Range(Cells(rw, "I"), Cells(rw + addCount, "I")).Insert shift:=xlDown
   End If
'Resets Process Counter and Find Next Change In Process
    procCount = 1
     Do Until Cells(rw, "I") <> Cells(rw + 1, "I")
      rw = rw + 1
  GoTo start
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 25, 2011 at 08:43:50

Hi Derby,

Thanks for your help with this. I ran the macro on a copy of the report, the required rows were inserted based on the process count already there, however the data on other columns (for existing process) seems to have been deleted or replaced by the newly added rows.

In the process file I have data from col A to Q, could you assist in modifying the code so the newly added process names does not replace the contents of the existing process names on other columns. Leaving the other columns blank except for the process name col I would be great.

Also on the col J, for the newly added process names we need to enter the company numbers. There are 63 numbers in total and I was updating the missing numbers to the process list by giving vlookup. Could you pls advise if there is any
formula/shortcut to do this as well. (ie add the remaining numbers in col J to newly added rows by referring to the std list of numbers in separate sheet)

Thank you.

Report •

April 25, 2011 at 10:30:11
It would have been nice if you had included all of this information in your original post.

When members of a forum write code to solve an issue, we can only write code based on the information given. Then we have to set up a test workbook to make sure the code does what we think it is supposed to do.

When additional requirements are added afterwards it is not always a simple "addition" that is needed to handle the new requests. Sometimes it's a complete rewrite of the code.

In addition, a test workbook needs to be set up (again) so that the new code can be tested.

Whenever possible, please try to include all requirements with your original question.

Re: the values in Column J

If an existing Process Name (e.g. 123) in Column I has e.g. Company XYZ in Column J, is it save to assume that all of the newly added 123's should have Company XYZ in Column J?

You are currently using VLOOKUP on the value in Column I to pull the company name from another sheet, meaning that copying the formula along with the process name to the newly added rows should get the same results, correct?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

April 25, 2011 at 12:28:15
Sorry for the inconvenience caused. I'll take care of such things in future.
re: the values in col j
for ex, let's say we have a standard list of numbers 1 to 63 on a different sheet. Now on the 'process' sheet if an existing process 'abc' on col I2:I4 has 3 lines with numbers 1, 2 & 3 in col J2:J4 then the remaining 60 rows added should copy the process name from above and update the remaining numbers 4 to 63 individually in col j.

Regarding the vlookup, from the other sheet with std list of 63 numbers I was actually giving vlookup to the col j on 'process' sheet, then copy all the actual remaining numbers(filtering the #N/A) from the numbers list and pasting into the 'process' sheet. There are a lot of processes and updating manually would take a lot of time. I'll provide you the standard list of 63 numbers tomorrow. Let me know if you need any other information .

Thanks again.

Report •

April 26, 2011 at 02:55:15

Here is the standard list of 63 numbers..


So, each 'process' name would have all these numbers in col J including the existing and newly added processes.

Hope this is Ok.

Can you pls assist with this?

Many thanks.

Report •

April 26, 2011 at 07:33:40
re: "So, each 'process' name would have all these numbers in col J..."

Are you saying that it should loook something like this?

First Process1 100
Second Process1 105
(60 more Process1's)
Sixty Third Process1 993

First Process2 100
Second Process2 105
(60 more Process2's)
Sixty Third Process2 993

How are you using VLOOKUP to pull in the numbers if you have up to 63 occurrences of a given Process Name? VLOOKUP would only find the first occurrence.

Another question: In your OP, you said that the Process Names already exist but there may not be 63 occurences of some of them. I therefore assume that the Process Names that do exist already have numbers associated with them in Column J. What numbers are already assigned? Is it safe to assume that if there are e.g. 5 occurrences of a given Process Name, then the first 5 numbers in your list are already used? (e.g. 100 - 206)?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 26, 2011 at 08:24:34


Regarding your first question..

yes it should look something like this, however, the co. numbers on col. J may be different depending on the process and not necessarily in order of the std number list as above. Co. 700 may have "process A" while others may not have this process at all.

For ex:Existing "Process A" may have only one line and number "740" on col J, in this case we need to show all 63 numbers for "Process A" Newly added rows should show all numbers except 740 in this case.

Pls see below:

First Process1 200 (existing)
Second Process1 105 (existing)
Third Process 1 100 (existing)
(60 more Process1's) : numbers should show (std list of 63 minus already used 200-105-100) (newly added)

First Process2 700 (existing)
Second Process2 400 (existing)
61 more Process 2s Remaining nos (63 minus already used 700 & 400) (newly added)

Regarding vlookup..

I was using vlookup from other sheet that contains all the 63 numbers to the 'Process sheet' process by process to see what numbers are missing in a particular process and then updating the missing numbers manually.

About the last question, the numbers already assigned on the existing process depends on process to process and not same for all processes.

Let me know if you need anything else.
Thank you.

Report •

April 26, 2011 at 09:52:08
Now do you see what I meant about including all requirements in your original question?

We have gone from a fairly simple request:

"count number of process name in col I ... if less than 63 insert the remaining rows."


count number of process name in col I ... if less than 63 insert the remaining rows, but don't duplicate or alter any existing data in other columns.

Then after the additional rows have been inserted, go back and read the values in column J of the original data and place any missing values from a list on another sheet into Column J of the newly added rows.

How could anyone have known about the additional (and fairly complicated) requirements based on your original question?

This is going to require significant changes to the original code that I suggested, which only addressed the simplest requirement.

I'll work on this as my time allows, but if there are any other "additions" you'll have to work those out on your own.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

April 26, 2011 at 10:20:27

Yes I do agree I should've all these info in the OP. As you mentioned the significant part of the code needs to be changed and written again because of missing info. Sorry!! In future whenever possible I'll include all the info with examples as you've illustrated above. I wasnt sure whether it is possible to automate the col J part so felt not to ask initially. Thought when the required rows have been inserted, will look out for some kind of formula to fill in the col J part.

Sure.. there are no more additions to this request.
Many thanks for all your help with this.

Report •

Ask Question