Option Explicit
Dim jJ As Long, lRow As Long, lCol As Byte: Dim Timer_ As Double
Dim WF As Object, Min_ As Integer, Max_ As Integer, wW As Integer
Dim MyAdd As String: Dim Yes As Boolean
Sub CopyRowsWhen()
Dim Rng As Range, Sh As Worksheet, RgD As Range, RgC As Range
Sheet1.Select: Set Sh = Sheet2
Timer_ = Timer
lCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
lRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Sh.[A1].Resize(9 + lRow, lCol + 3).Clear
[A1].Resize(lRow, 2).Interior.ColorIndex = 0
For jJ = 4 To lRow
Set Rng = Cells(jJ, 2 + lCol).End(xlToLeft)
If Rng.Offset(, -1).Value = "" Then
Cells(Rng.Row, "A").Interior.ColorIndex = 39
Else
Set Rng = Cells(jJ, "A")
If Rng.Value = "" Then Set Rng = Rng.End(xlToRight)
If Rng.Offset(, 1).Value = "" Then
Cells(Rng.Row, "A").Interior.ColorIndex = 38
Else
With Sh.Cells(65500, lCol + 2).End(xlUp).Offset(2)
.Value = jJ
.Offset(, -lCol - 1).Resize(, lCol).Value = Cells(jJ, 1).Resize(, lCol).Value
End With
End If
End If
Next jJ
Application.ScreenUpdating = False: Sh.Select
lRow = Sh.Cells(65500, lCol + 2).End(xlUp).Row
For jJ = lRow To 2 Step -2
Set RgD = Cells(jJ, "A")
If RgD.Value = "" Then Set RgD = RgD.End(xlToRight)
Set RgC = RgD.End(xlToRight)
If Range(RgD, RgC).Cells.Count Mod 2 = 1 Then
RgD.Resize(2).EntireRow.Delete
Else
Set RgC = Cells(jJ, lCol)
If RgC.Value = "" Then Set RgC = RgC.End(xlToLeft)
Set RgD = RgC.End(xlToLeft)
If Range(RgD, RgC).Cells.Count Mod 2 = 1 Then
RgD.Resize(2).EntireRow.Delete
End If
End If
Next jJ
lRow = Sh.Cells(65500, lCol + 2).End(xlUp).Row
Set WF = Application.WorksheetFunction
Set RgC = Cells(65500, 1)
For jJ = lRow To 2 Step -2
Set Rng = Cells(jJ, "A").Resize(, lCol)
Min_ = WF.Min(Rng): Max_ = WF.Max(Rng)
For wW = Min_ To Max_
Set RgD = Rng.Find(wW, , xlFormulas, xlWhole)
If Not RgD Is Nothing Then
MyAdd = RgD.Address
Do
If RgD.Offset(, 1).Value = "" And (RgD.Column = 1 Or RgD.Offset(, -1).Value = "") Then
Set RgC = Union(RgC, RgD.Resize(2))
Yes = True: Exit For
End If
Set RgD = Rng.FindNext(RgD)
Loop While Not RgD Is Nothing And RgD.Address <> MyAdd
End If
Next wW
Next jJ
RgC.EntireRow.Delete
MsgBox Timer() - Timer_: Set WF = Nothing
End Sub