VBA Code to Insert Variable # of Rows to be Added Below Data So All data sets have Same Number of Rows

Cybermiser

New Member
Joined
Sep 15, 2016
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hello,
I'm new to VBA but would like to see if anyone could help me with my VBA code. Attached is a screenshot of the expected result.

Process: I copied column from an online index which has variable number of rows per individual.
In order to transpose the data and align data properly, each set of data "Record" must have the same number of rows.

Rows assigned to a record could range from 8 to 13
Individuals could be alone or part of a family.

Example:
Record #1 - rows 1-9 (9 rows) (that I'll call "Q" through "Z")
Group #42

Record #2 - rows 10-18 (9 rows) (that I'll call "Q2" through "Z2")
Group #42

Record #3 - rows 19-30 (12 rows) (that I'll call "Q3" through "Z3")
Group #43
...etc
If I look at it, I can see that there's a value assigned to each individual, however as well as a "group number" that immediately follows. (ie 1, 42; 2, 42; 3, 43 etc) So that's what I think could be used to determine how many rows to insert.
Data is manually pasted into Column A - no other data present on spreadsheet or workbook.

VBA Code Steps:
Remove the word Close in the last cell
Assign incremental value to each row starting in B1 (assuming that I have to take this step before we can determine how many rows to insert)
Count the number of rows per individual starting with 1 (Originating Cell that I'll call "Q")
If subsequent cell increments by 1 AND the value after it either is the same or increments by one then (Q + 1 (ie "Z"):

Count number of cells between and including Q and Z.

If count = 10 then insert three rows after Z
If count = 9 > insert 4 rows after Z
If count = 13 then go to next set of data

Repeat with next set starting with "Q2"

Thanks for any help!
 

Attachments

  • Insert Rows and Transpose.jpg
    Insert Rows and Transpose.jpg
    120.8 KB · Views: 6

Cybermiser

New Member
Joined
Sep 15, 2016
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
OK, assuming that 3 numbers in consecutive cells happens at the start of every record and never in the middle or at the end of a record then the change is easy - just remove this code

Rich (BB code):
If IsNumeric(a(fr + j - 1, 1) & a(fr + j, 1) & a(fr + j + 1, 1) & 0) And a(fr + j, 1) = a(fr + j + 1, 1) Then
Thanks Peter! You are a spectre from the Gods!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,516
Office Version
  1. 365
Platform
  1. Windows
Thanks Peter!
You are welcome once again. Perhaps you should change which post is marked as the accepted solution if the previous one ended up not quite doing the job?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,101
Messages
5,576,132
Members
412,699
Latest member
Dmetcalf2021
Top