Macro that finds a semicolon, then copies and inserts rows, and removes some data

[Zlerp]

Hello All,

I need some help creating a macro in excell that will help me complete my work. What the macro will do is go through column Y and search for any cell that contains a semicolon in between numbers. if it finds a semicolon, i need the macro to copy the entire row, Insert the copied row below it, and remove the semi colon and split the numbers in the cell so that there is only one on each row in column.

For example.
if i had a worksheet with data on it.
the Data in column Y would be starting at Y5 to the last row of data
IF YOU ARE HAVING TROUBLE UNDERSTANDING MY REQUEST PLEASE LOOK AT THE ATTACHMENT.

****THE DATA FOR THE REAL WORKBOOK WILL START AT Y5****

As you can see in the Data below.
column Y contains either a blank cell, contains one number, or contains a couple or more numbers seperated with a semi colon.
If and only If a cell in column Y contains a semicolon, then i want that row copied and pasted directly below the amount of times that there are different numbers. So where row 4 is (the row containing all d's) This gets copied one time so there are 2 of the same rows, and row 5 (the row containing all e's) should be copied and inserted 2 times so that there are 3 rows of this data.

Then once that is complete, I need column Y to only contain one number (the numbers are split by semicolon) on each of those rows that we just copy and pasted.
These steps need to continue for the entire column of Y from Y5 to the last row of data.

Sorry for the long question. Just dont really know how to word it.

I HAVE ATTACHED AN EXAMPLE OF WHAT I WANT. THE INITIAL DATA SHOWS WHAT I WILL BE RECIEVING. THE FINAL DATA IS WHAT I WANT.
***NOTICE THE HIGHLIGHTED REGIONS ON BOTH SHEETS***

PS: the numbers vary in size, sometimes they are 3 digits, sometimes 4, sometimes 8... its random. Just needs to be split by Semicolon.

12198


V
W
X
Y


a
a
a
12345


b
b
b



c
c
c
78910


d
d
d
86594;56932


e
e
e
99658;45633;85647


f
f
f
64985


g
g
g
76548


h
h
h
16453


i
i
i
78945

[Bob Phillips]

Public Sub BasicLoop()
Dim rowLast As Long
Dim cntNums As Long
Dim ary As Variant
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

rowLast = .Cells(.Rows.Count, "Y").End(xlUp).Row
For i = rowLast To 5 Step -1

If InStr(.Cells(i, "Y").Value, ";") > 0 Then

ary = Split(.Cells(i, "Y").Value, ";")
cntNums = UBound(ary) - LBound(ary) + 1
.Rows(i + 1).Resize(cntNums - 1).Insert
.Cells(i, "V").Resize(, 3).AutoFill .Cells(i, "V").Resize(cntNums, 3)
.Cells(i, "Y").Resize(cntNums) = Application.Transpose(ary)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Zlerp]

Works great! Made a few changes so it works with my data but i got what i needed and I thank you for that!

:hi: