Finding and deleting last column with header if sum is 0

Question

I want to create a macro that finds the last column with a header and deletes it only if the sum of that column is equals to zero. Here's what I've tried so far:

Dim LastCol As Long, Dim i As Long

With ThisWorkbook.Sheets("Sheet1")
For i = Range("A1").Column To LastCol Step -1
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    If Application.Sum(Cells(1, i).Resize(LR, 1)) = 0 Then
        Columns(i).EntireColumn.Delete 
    End With
Next i

Thanks in advance for your attention and help

Answer 1

Give it a try like this:

Option Explicit

Public Sub TestMe()

    Dim LastCol As Long, i As Long, LR As Long
    LR = 5
    With ThisWorkbook.Sheets(1)
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For i = LastCol  to .Range("A1").Column Step -1
            If Application.Sum(.Cells(1, i).Resize(LR, 1)) = 0 Then
                .Columns(i).EntireColumn.Delete
            End If
        Next i
    End With

End Sub

This is what I have changed:

• Remove the 2. "Dim here -> Dim LastCol As Long, i As Long;
• Refer correctly to the With - End with - it is a good practice to have it, but it should be bringing you something - thus, you should put a dot before the ranges - .Range("A1").Co.., .Columns(i).Entir..., .Cells(1, .Columns
• You need End If at the end of the condition;
• Adding [Option Explicit][1] on top helps you find similar errors before running the code;
• You are refering to lastcol before assigning a value. Thus, it should go before the loop;
• The loop starts at column 1 and steps -1. It needs to be reversed (10x @Scott Craner);