Home

Help Coding to Delete Certain Sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-725547%22%20slang%3D%22en-US%22%3EHelp%20Coding%20to%20Delete%20Certain%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725547%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EWeekly%20I%20have%20to%20sort%20through%20350%20sheets%20and%20delete%20the%20ones%20that%20basically%20do%20not%20have%20any%20writing%20below%20cell%2020.%20Is%20there%20a%20Macro%20code%20I%20could%20enter%20that%20deletes%20all%20worksheets%20that%20don%E2%80%99t%20have%20any%20values%20below%20row%2020%3F%20If%20so%20could%20you%20help%3F%20This%20would%20take%20a%20lot%20of%20time%20off%20of%20this%20weekly%20task!%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-725547%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-752578%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Coding%20to%20Delete%20Certain%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-752578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367845%22%20target%3D%22_blank%22%3E%40jimmyg123%3C%2FA%3E%26nbsp%3BHow's%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESub%20DelSheets()%0A%20%20%20%20Dim%20ws%20As%20Worksheet%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20%0A%20%20%20%20%20For%20Each%20ws%20In%20ActiveWorkbook.Worksheets%0A%20%20%20%20%20%20%20%20If%20Application.WorksheetFunction.CountA(ws.Range(%2221%3A1048576%22))%20%3D%200%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.DisplayAlerts%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ws.Delete%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.DisplayAlerts%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20ws%0A%20%20%20%20%0A%20%20%20%20MsgBox%20i%20%26amp%3B%20%22%20sheets%20were%20deleted%22%2C%20vbOKOnly%20%2B%20vbInformation%2C%20%22Sheets%20deleted%22%0A%20%20%20%20%0AEnd%20Sub%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
jimmyg123
Occasional Visitor
Hi,

Weekly I have to sort through 350 sheets and delete the ones that basically do not have any writing below cell 20. Is there a Macro code I could enter that deletes all worksheets that don’t have any values below row 20? If so could you help? This would take a lot of time off of this weekly task! Thanks!
1 Reply

@jimmyg123 How's this:

 

Sub DelSheets()
    Dim ws As Worksheet
    Dim i As Long
    
     For Each ws In ActiveWorkbook.Worksheets
        If Application.WorksheetFunction.CountA(ws.Range("21:1048576")) = 0 Then
            Application.DisplayAlerts = False
                ws.Delete
                i = i + 1
            Application.DisplayAlerts = True
        End If
    Next ws
    
    MsgBox i & " sheets were deleted", vbOKOnly + vbInformation, "Sheets deleted"
    
End Sub