Upgrade the code to ignore empty sheets

[An Quala]

Hello, I would like to upgrade these codes to not run if the sheets are empty. So the problem is usually 2 of these 3 sheets are empty when I run the code but it works for all 3 sheets, so I don't want it to run on empty sheets. If any of these 3 sheets are empty (Which could be found by checking the cell A2 of any sheet) then it should ignore it.

Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        Worksheets(x).Cells.Interior.Color = xlNone
        c = va
    Next

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          s = s & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1 & " "     'collect the last rows and use the space as separator
     Next
     sp = Split(s)                                              'split string on space
     MsgBox sp(0) & " SP, " & sp(1) & " SB and " & sp(2) & " SD targets have been optimized in " & Format(dTime, "0.0") & "seconds."    'make the text
     
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
    
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("F:G,J:Q,S:Y,AA:AS").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
    End With

I think both of the above codes might have a same solution, kindly help, thank you.

 

[bebo021999]

Since blank sheet will have .usedrange = first cell A1 then

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
    Set c = Worksheets(x).UsedRange
    If c.Address <> "$A$1" Then
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        Worksheets(x).Cells.Interior.Color = xlNone
        c = va
    End If
Next

 

[An Quala]

Since blank sheet will have .usedrange = first cell A1 then

Thank you @bebo021999, can you please tell me the solution for the second code as well.

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
          s = s & Sheets(CStr(x)).Range("A" & Rows.Count).End(xlUp).Row - 1 & " "     'collect the last rows and use the space as separator
     Next
     sp = Split(s)                                              'split string on space
     MsgBox sp(0) & " SP, " & sp(1) & " SB and " & sp(2) & " SD targets have been optimized in " & Format(dTime, "0.0") & "seconds."    'make the text
     
    With ThisWorkbook.Sheets(Array("Sponsored Products Campaigns", "Sponsored Display Campaigns", "Sponsored Brands Campaigns"))
        .Copy
    End With
    
    With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("F:G,J:Q,S:Y,AA:AS").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
    End With

 

[bebo021999]

Same If right before main code

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
    If Worksheets(x).UsedRange.Address <> "$A$1" Then
................................< your main code.....................
end if
end sub

 

[An Quala]

Same If right before main code

Thanks and what about this part?

 With ActiveWorkbook
        .Worksheets("Sponsored Products Campaigns").Range("F:G,J:Q,S:Y,AA:AS").EntireColumn.Delete
        .Worksheets("Sponsored Brands Campaigns").Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
        .Worksheets("Sponsored Display Campaigns").Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
    End With

 

[bebo021999]

Like this?.

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
    If Worksheets(x).UsedRange.Address <> "$A$1" Then
          Select case  x 
                Case "Sponsored Products Campaigns"
                            Worksheets(x).range("F:G,J:Q,S:Y,AA:AS").EntireColumn.Delete
                Case "Sponsored Brands Campaigns"
           end select                ....
end if
NExt