PDA

View Full Version : [SOLVED] Select Cell Based on Length of Cell Value



BenChod
06-06-2017, 09:12 AM
Hello All -

I have a table with three columns. The third column has a set of IDs, six numeric characters in length. Some of the cells in the third column will have two sets of IDs concatenated. What I am currently doing manually is the following:


For each cell that has the two IDs concatenated, I copy that entire row, insert a row below, and paste.
Then I delete the last six digits from the row where I copied the data
Then I delete the first six digits from the row where I pasted the data


There are hundreds of cells within the table where the cells in the third column has two IDs concatenated. I am hoping someone can assist on how to automate where the code will look at the cells in the third column until the end of the row where the length is 12, insert a row below, copy and paste, and parse the IDs. I have no idea where to begin on how to write the code and hoping someone can assist.

Attached is the excel file with the data set illustrating the desired output. Any help is greatly appreciated.

19409

mdmackillop
06-06-2017, 09:50 AM
Sub Test()
lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
If Len(Cells(i, 3)) = 12 Then
Cells(i + 1, 1).EntireRow.Insert
Cells(i, 1).Resize(2, 3).FillDown
Cells(i, 3).Value = Left(Cells(i, 3), 6)
Cells(i + 1, 3).Value = Right(Cells(i + 1, 3), 6)
End If
Next i
End Sub

BenChod
06-06-2017, 10:48 AM
Thank you so much sir. It worked like a charm.