This macro:

1. Duplicates every row that has a value in G and adds them to the bottom of sheet at first empty row.
2. On those duplicated rows, it swaps the values in Column G with the values in Column F.

It works beautifully when there is data to consider:


Sub Macro6()
'
' Macro6 Macro
'
Dim valA
Dim valB
Dim cel As Range
Dim rng As Range
Dim lastrow As Long

    With Application
        .ScreenUpdating = True
        .EnableEvents = False
    End With

    'Get first blank row
    lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    
    'Build all of Col g
    Set rng = Range("G5:G" & lastrow - 1)
        
    'Check all of Col g for values
    For Each cel In rng
        'If has value
        If cel <> "" Then
            'Copy to blank row at end
            Range("A" & cel.Row & ":AQ" & cel.Row).Copy Range("A" & lastrow)
            'Save Col F value
            valA = Range("F" & lastrow)
            'Save Col G value
            valB = Range("G" & lastrow)
            'Copy G to F
            Range("G" & lastrow).Copy Range("F" & lastrow)
            'Put old F to G
            Range("G" & lastrow) = valA
            lastrow = lastrow + 1
        End If
    Next cel
    
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    
    Set cel = Nothing
    Set rng = Nothing
End Sub
Where it fails is when there is no data to consider.

My header starts on Row 4 and the columns go out to Column AQ.

On some of the sheets this will be run on as part of a larger macro, it is legitimate for those sheets to sometimes have no data, just the header.

When there is no data, this macro repeats the header three times, which messes up COUNT and SUMPRODUCT calculations on my summary sheet.

Any advice on how to get it to ignore those sheets that have no data? Like I said, it works perfectly provided there is data under the header - I just can't guarantee every sheet will have data!

Thank you!