CODEWITHSUNDEEP
excelvba
eyeScream
eyeScream

Reputation: 71

Defined range changes with each loop (HOW TO STOP)

I set a range and then summed each column in that range. When I then test each summed column to check it's value (and delete if it equals zero), the range keeps changing. How do I stop or change this?

Basically I just want to sum each column from C to the last column and if the column equals zero I want to delete that column. I don't even actually need to see the sum of the column. I just want to test it and if it is zero then delete that column. Once I've cleaned it up I will continue manipulating the data. My data does have headings. The number of rows in the data is dynamic. If there is an easier way please let me know. This is what I was trying (the reason my ttlRow looks off is because in column A I have entered "Total" in the first empty row).

Dim ttlRow As Range
Dim x As Long
Dim y As Long
y = Cells(1, Columns.Count).End(xlToLeft).Column
x = Cells(Rows.Count, 1).End(xlUp).Row
Set ttlRow = Range(Cells(x, 3), Cells(x, y))
Range("C1").End(xlDown).Offset(1, 0).Formula = "=SUM(" & Range(Cells(2, 3), Cells(x - 1, 3)).Address(False, False) & ")"
ttlRow.FillRight

For Each cell In ttlRow
    If cell = 0 Then
        cell.EntireColumn.Delete
    End If
Next

Each time the loop is run and a column deleted, it changes the length of ttlRow so it never ends up running through all the columns to check. How do I fix this?

Upvotes: 0

Views: 43

Answers (1)

Mikku
Mikku

Reputation: 6664

Use this:

Dim x As Long
Dim y As Long
Dim i as Integer

    y = Cells(1, Columns.Count).End(xlToLeft).Column
    x = Cells(Rows.Count, 3).End(xlUp).Row

For i = y To 3 Step -1

    If WorksheetFunction.Sum(Range(Cells(2, i), Cells(x, i))) = 0 Then
        Columns(i).EntireColumn.Delete
    End If

Next

Upvotes: 1

Related Questions