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: 20
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!
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top