Excel VBA: Loop through range and delete columns with all 0 values [duplicate]

Question

I've just started learning VBA and I've been stuck on a problem for a while and hope someone can help.

What I want to do is to first loop through each row in a dynamic range that I copied from another sheet starting at column 8 out of 58 columns and check if the value in each cell in the row is either "0" or another value.

If the value is "0", then I want it to go to the next row and check to see if that value is also zero. Continue this until the very last row if all cell values are 0 or go to the next column if there is a cell value other than 0.

If all the cells in the column are 0, I want to delete the column before going to the next column. Repeat this process for all the columns in the range.

The end result would be that all the columns in the initial range with only 0 values will be deleted while columns with values will remain.

Can someone please help?

Below is the code I have so far:

Dim CopyRange as Range 
Dim iRow as Range

Set CopyRange = Range("H2", Range("H2").End(xldown))

For each iRow in CopyRange

if iRow = "0" then
activecell.offset(1,0).select
else

end if 

next iRow

*delete column *go to next column and repeat for remaining columns

Gowtham Shiva · Accepted Answer · 2017-09-08 17:44:56Z

0

Try this simple code,

Sub searchForZero()
Dim i As Long, j As Long
For i = 58 To 8 Step -1
    j = Cells(Rows.Count, i).End(xlUp).Row
    If Application.WorksheetFunction.CountIf(Range(Cells(1, i), Cells(j, i)), 0) = j - 1 Then
        Columns(i).Delete
    End If
    Next i
End Sub

This code loops from column 58 to 8 and deletes the column which has all 0's in it.

Note:- This works only if your data start at row 1. If not you have to modify the 'j' variable accordingly

edited Sep 8, 2017 at 17:44

answered Sep 8, 2017 at 16:40

Gowtham Shiva

3,8852 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlie Chen Over a year ago

thanks. how would I modify the j variable? I have a header tab on the first row that I want to ignore.

Gowtham Shiva Over a year ago

@CharlieChen I have modified the code. Try it out

Collectives™ on Stack Overflow

Excel VBA: Loop through range and delete columns with all 0 values [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related