Delete two columns if right value is zero

Question

I have two columns. The first column contains the code and the second column contains the null values (0).

Is there a way that I can delete these 2 columns, if the second column value is equal to zero?

I tried using this, but it only deletes the zero values in the second column.

Sub ClearZero()
    For Each cell In Range("C6:D4005")
        If cell.Value = "0" Then cell.Clear
    Next
End Sub

Jayvee · Accepted Answer · 2021-05-26T08:25:53.123

2

you can do it in this way:

Sub ClearZero()

For Each cell In Range("D6:D4005")
 If cell.Value = "0" Then 
    cell.Clear
    cell.offset(0, -1).Clear
 End If

 Next

End Sub

edited May 26 '21 at 08:25

answered May 26 '21 at 08:21

Jayvee

10,670
3
29
40

1

Correction - use `For Each cell In Range("D6:D4005")`, because otherwise if a cell in C column evaluates to 0 then it will delete a cell from B column. And even if that's never going to happen in this dataset it's worth thinking about, plus it would halve the length of the loop. – Spencer Barnes May 26 '21 at 08:24
1

thanks @SpencerBarnes, very good point. I've updated the answer. – Jayvee May 26 '21 at 08:26

score 0 · Answer 2 · answered May 26 '21 at 09:16

Another way to achieve what you want without looping.

LOGIC:

Identify the worksheet you are going to work with.
Remove any autofilter.
Check if the column D has 0 anywhere.
Find last row in column D. Better than hardcoding D4005
Construct your range.
Filter column D based on 0.
Identify the filtered range.
Clear the cells in 2 columns without looping.

CODE:

Is this what you are trying? I have commented the code so you should not have a problem understanding it but if you do them simply ask :)

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim FilteredRange As Range
    
    '~~> Set this to the relevant sheet
    Set ws = Sheet1
    
    With ws
        '~~> Remove any filters
        .AutoFilterMode = False
        
        '~~> Find last row
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        
        '~~> Check if the column D has 0 anywhere
        If Application.WorksheetFunction.CountIf(.Range("D1:D" & lRow), 0) Then
           '~~> Filter column D on 0
           .Range("D1:D" & lRow).AutoFilter Field:=1, Criteria1:=0
            
            '~~> Identify your range
            Set FilteredRange = .AutoFilter.Range
            
            '~~> Clear the range in 1 go!
            FilteredRange.Offset(1, 0).Resize(FilteredRange.Rows.Count - 1).Clear
            FilteredRange.Offset(1, -1).Resize(FilteredRange.Rows.Count - 1).Clear
        End If
        
        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

BEFORE:

AFTER:

Delete two columns if right value is zero

2 Answers2