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

[Cybermiser]

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!

 

[Cybermiser]

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

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!

 

[Peter_SSs]

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?