Delete the empty sheets from a created file

[An Quala]

Hello, I use the following code to make a new file after deleting the certain columns from the old file, but I want to add one more thing here, if any of the three sheets ('Sponsored Products Campaigns', 'Sponsored Brands Campaigns' ,'Sponsored Display Campaigns') are empty (i.e. A2 is empty) then don´t import that sheet at all. Ideally don´t delete the columns as well since that is unnecessary in case of empty sheets but ultimately I don´t want those sheets in the new file.

Can anyone please help me do that?

For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
    If Worksheets(x).UsedRange.Address <> "$A$1" Then
 Campaigns "          Select Case x"
                Case "Sponsored Products Campaigns"
                            Worksheets(x).Range("F:G,J:Q,S:AA,AC:AU").EntireColumn.Delete
                Case "Sponsored Brands"
                            Worksheets(x).Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
                Case "Sponsored Display Campaigns"
                            Worksheets(x).Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
          End Select
     End If
Next

 

[An Quala]

Bump

 

[An Quala]

Bump

 

[An Quala]

Bump

 

[Joe4]

So if you want to skip that delete columns section when Cell A2 of that sheet is blank, I think all you need to do is update this line:

    If Worksheets(x).UsedRange.Address <> "$A$1" Then

to this:

    If (Worksheets(x).UsedRange.Address <> "$A$1") And (Worksheets(x).Range("A2")<>"") Then

 

[An Quala]

If (Worksheets(x).UsedRange.Address <> "$A$1") And (Worksheets(x).Range("A2")<>"") Then

Hi, thank you! It is still creating new sheets, just not doing deleting the columns now which is also a good a thing.
But I don´t want the other sheets that are blank in the final file, can I just delete those sheets at the end?

Thanks!

 

[Joe4]

Hi, thank you! It is still creating new sheets, just not doing deleting the columns now which is also a good a thing.
But I don´t want the other sheets that are blank in the final file, can I just delete those sheets at the end?

Thanks!

You haven't posted the section of your code that creates the new sheets.
You only posted the section of the code that does the column deletions.
If you want other sections updated too, you need to post that code.

 

[An Quala]

If you want other sections updated too, you need to post that code.

Sorry my bad, now here is the full code. Thank you!

     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
    
For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
    If Worksheets(x).UsedRange.Address <> "$A$1" And (Worksheets(x).Range("A2") <> "") Then
          Select Case x
                Case "Sponsored Products Campaigns"
                            Worksheets(x).Range("F:G,J:Q,S:AA,AC:AU").EntireColumn.Delete
                Case "Sponsored Brands Campaigns"
                            Worksheets(x).Range("E:F,J:N,P:U,W:AY").EntireColumn.Delete
                Case "Sponsored Display Campaigns"
                            Worksheets(x).Range("E:E,G:G,I:O,Q:Y,AA:AP").EntireColumn.Delete
          End Select
     End If
Next
    
    If MsgBox("Extra columns have been deleted" & vbNewLine & vbNewLine & _
        "Do you want to save as a new file?", vbYesNo, "Confirm") = vbNo Then
        ActiveWorkbook.Close SaveChanges:=0
        Exit Sub
    End If
    
    Dim FileName As Variant
    FileName = Application.GetSaveAsFilename(FileFilter:="Microsoft Excel file (*.xlsx), *.xlsx")
    ActiveWorkbook.SaveAs FileName:=FileName
    
    
Next

 

[Joe4]

Maybe take a little bit of a different (simpler) approach:
1. Copy all three sheets over to a new
2. Loop through each of the three sheets in the new file, and if "A2" is blank, then delete that sheet; otherwise run the column delete code for that particular sheet
3. Save the file

 

[An Quala]

Maybe take a little bit of a different (simpler) approach:
1. Copy all three sheets over to a new
2. Loop through each of the three sheets in the new file, and if "A2" is blank, then delete that sheet; otherwise run the column delete code for that particular sheet
3. Save the file

Is it possible for you to edit the code like this if easier? I hardly know VBA basics, thank you!