tinamiller1
12-11-2014, 09:22 AM
I have several workbooks that have 52 columns and 5000 rows. I receive these on a weekly basis and have some VBA code to clean the data in order to get all the rows distinct so I can import to my SQL Server. The VBA I have now gets these workbooks to a certain point but then I have to manually do a bit of clean-up since I cannot get my VBA to work to copy an entire row based on a column that has a comma. Example date:
ID State Segment AccountID
PG1 CA National 1000000
PG2 CA National 1000100
PG3 CA National 1001001, 1001002
PG4 NY Local 5555055
PG5 NY Individual IND190
PG6 NY National 5555056, 5555057, 5555058, 5555059
So in the example above, I would want after the code is run, the information to look like this:
ID State Segment AccountID
PG1 CA National 1000000
PG2 CA National 1000100
PG3 CA National 1001001
PG3 CA National 1001002
PG4 NY Local 5555055
PG5 NY Individual IND190
PG6 NY National 5555056
PG6 NY National 5555057
PG6 NY National 5555058
PG6 NY National 5555059
Here is the code I have but I cannot figure out how to code the comma.
Sub Main()
'Add rows when , is in cell I to split the accountID into row so row is distinct
Dim cell As Range: Application.ScreenUpdating = False
Set cell = Range("I" & Rows.Count).End(xlUp) ' Last cell in column I
Do
Application.StatusBar = "Processing row " & cell.Row
AddRows cell: If cell.Row = 1 Then Exit Do
Set cell = cell.Offset(-1): If cell.Row Mod 20 = 0 Then DoEvents
Loop
Application.StatusBar = False
End Sub
Sub AddRows(ByRef cell As Range)
If cell = 1 Then cell.Next = 1: Exit Sub
If cell < 2 Or Trim(cell.Offset(1)) = "" Then Exit Sub
cell(1, 2) = 1: Cols = cell.Parent.UsedRange.Columns.Count - 2
For i = 1 To cell - 1
cell(2, 1).EntireRow.Insert: cell(2, 2) = cell - i + 1
Next
cell(1, 3).Resize(cell, Cols).FillDown
End Sub
ID State Segment AccountID
PG1 CA National 1000000
PG2 CA National 1000100
PG3 CA National 1001001, 1001002
PG4 NY Local 5555055
PG5 NY Individual IND190
PG6 NY National 5555056, 5555057, 5555058, 5555059
So in the example above, I would want after the code is run, the information to look like this:
ID State Segment AccountID
PG1 CA National 1000000
PG2 CA National 1000100
PG3 CA National 1001001
PG3 CA National 1001002
PG4 NY Local 5555055
PG5 NY Individual IND190
PG6 NY National 5555056
PG6 NY National 5555057
PG6 NY National 5555058
PG6 NY National 5555059
Here is the code I have but I cannot figure out how to code the comma.
Sub Main()
'Add rows when , is in cell I to split the accountID into row so row is distinct
Dim cell As Range: Application.ScreenUpdating = False
Set cell = Range("I" & Rows.Count).End(xlUp) ' Last cell in column I
Do
Application.StatusBar = "Processing row " & cell.Row
AddRows cell: If cell.Row = 1 Then Exit Do
Set cell = cell.Offset(-1): If cell.Row Mod 20 = 0 Then DoEvents
Loop
Application.StatusBar = False
End Sub
Sub AddRows(ByRef cell As Range)
If cell = 1 Then cell.Next = 1: Exit Sub
If cell < 2 Or Trim(cell.Offset(1)) = "" Then Exit Sub
cell(1, 2) = 1: Cols = cell.Parent.UsedRange.Columns.Count - 2
For i = 1 To cell - 1
cell(2, 1).EntireRow.Insert: cell(2, 2) = cell - i + 1
Next
cell(1, 3).Resize(cell, Cols).FillDown
End Sub