should help you out I'm copying data from one table where there are calculations and appending it to a static table for archiving. The tables have the same number of columns. , You need to replace None with Nothing.
Mismatch between Range.Rows.Count and the rows I can return by passing row index to Range.Rows in Excel 2010 VBA
help you fix your problem Here is one way. Not the fastest but does the job. I have commented the code so you will not have a problem understanding it.
Dim ws As Worksheet
Dim lRow As Long, i As Long, j As Long
Dim tmpAr As Variant
'~~> Change this to the relevant sheet
Set ws = ThisWorkbook.Sheets("Sheet1")
'~~> Get last row in Col D. That is where we have to check for ":"
lRow = .Range("D" & .Rows.Count).End(xlUp).Row
'~~> Reverse loop the rows
For i = lRow To 1 Step -1
'~~> Check if cell in Col D has ":"
If InStr(1, .Range("D" & i).Value, ":") Then
'~~> Split on ":" and store in an array
tmpAr = Split(.Range("D" & i).Value, ":")
'~~> Loop through the array
For j = LBound(tmpAr) To UBound(tmpAr)
'~~> Insert a row in the next row
.Rows(i + 1).Insert Shift:=xlDown, _
'~~> Copy data from above as cell in Col D is different
.Rows(i).Copy .Rows(i + 1)
'~~> Add the new value to cell in Col D
.Cells(i + 1, 4).Value = tmpAr(j)
'~~> Delete the row
it should still fix some issue I see one issue right off the bat. When you are doing your ActiveSheet.Rows("startRow:endRow").EntireRow.Hidden = True you are not calling those variables, you are stating them as strings. Try this instead:
Type mismatch error in excel 2013 but works in excel 2010 when using ADODB.Recordset