|I'm making a couple of assumptions here...|
1 - In a given row, you will either have 1 or 2 entries in Columns AY:BA, never 3.
2 - You want to eliminate the extra data in the pair of rows once the original row is copied. In other words, let's say you have data in AY and BA. Once the row is copied, the first of the 2 rows should retain the data from AY, but not BA, and the second row should retain the data from BA, but not AY.
If those assumptions are correct, try this code. What it does is use COUNTA to determine if AY:BA contains more than 1 entry. If that is true, then it copies the row. Once the row is copied, it determines where those 2 pieces of data reside and deletes the extra data from each row.
'Turn off ScreenUpdating while macro runs
Application.ScreenUpdating = False
'Determine last row with data in Column A
lastRw = Range("A" & Rows.Count).End(xlUp).Row
'Loop through Rows in reverse order
For srcRw = lastRw To 2 Step -1
'If AY:BA in current Row contain more than one value, Copy/Insert Row
If WorksheetFunction.CountA(Range("AY" & srcRw & ":BA" & srcRw)) > 1 Then
Range("A" & srcRw).EntireRow.Copy
Range("A" & srcRw).Insert
'Erase the extra Data in the Rows with multiple values
If Range("AY" & srcRw) <> "" Then
Range("AZ" & srcRw & ":BA" & srcRw).ClearContents
Range("AY" & srcRw + 1).ClearContents
Range("BA" & srcRw).ClearContents
Range("AZ" & srcRw + 1).ClearContents
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.
message edited by DerbyDad03