How to delete specific columns(not entirely) in excel vba

Question

I am trying to delete columns when column T,U,V has 0 value at the same row. However, the code that i am using only deletes some of them and as shown below, there are still 0s at column T,U,V for some rows even though I already specify them to be deleted at specific cells that i have assigned. Which part of my code have i done wrong?

Dim j As Long

Dim lr As Long
lr = Sheets(1).Cells(Rows.Count, "U").End(xlUp).Row

For j = 1 To lr

   If Sheets(1).Cells(j, 21).Value = 0 And Sheets(1).Cells(j, 22).Value = 0 And Sheets(1).Cells(j, 23).Value = 0 Then
   Sheets(1).Range("T" & j & ":W" & j).Delete
      j = j + 1
      End If
      Next

After applying my code, there are still left overs rows with zeros.

so eventually i want it to be

If you delete a row, the row below it then takes that row number, so the code will skip it. That could be why? — newuser2967, Feb 20 '20 at 14:11
If you delete or add rows your loop needs to go backwards `For j = lr To 1 Step -1` otherwise deleting/adding changes the total row count and your loop jumps. — Pᴇʜ, Feb 20 '20 at 14:11
Even better - use `Union` to create a range to delete, and delete in one step at the end. Or use `Range.AutoFilter` and delete visible rows at the end. — BigBen, Feb 20 '20 at 14:19
@cena Have a look at the links above your question. It has been asked an answered several times before. Searching before asking can be very effective. — Pᴇʜ, Feb 20 '20 at 14:23
@ Pᴇʜ yeah i did search but i missed out the part on For j = lr To 1 Step -1. Thanks anyways — cena, Feb 20 '20 at 14:24
I think the question should be reopen, because linked answers talk about deleting **entire** rows, and OP needs to delete specific range and shifup — Foxfire And Burns And Burns, Feb 20 '20 at 14:38

score 3 · Accepted Answer · answered Feb 20 '20 at 14:14

3

Try:

Sub testr()

    Dim LastRow As Long, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

         LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row

        For i = LastRow To 2 Step -1
            If .Range("T" & i).Value = 0 And .Range("U" & i).Value = 0 And .Range("V" & i).Value = 0 Then
                .Rows(i).EntireRow.Delete
            End If
        Next i

    End With

End Sub

answered Feb 20 '20 at 14:14

Error 1004

7,877
3
23
46

is it possible not to delete the entirerow but just from column T to V? – cena Feb 20 '20 at 14:22
@cena do you really mean "delete" (remove the cells) or do you mean "clear" (remove the content)? – Pᴇʜ Feb 20 '20 at 14:24
@Pᴇʜ i think shifting the cells once it is being deleted means remove the cells?(not sure about that) – cena Feb 20 '20 at 14:26
@cena yes, the question is you want the rest of the data below to be shifted 1 row upwards or do you want to have an empty row where the zeros were? – Pᴇʜ Feb 20 '20 at 14:27
2

Try: `.Range("T" & i & ":V" & i).Delete Shift:=xlUp` – Error 1004 Feb 20 '20 at 14:27
@Pᴇʜ shifted 1 row upwards. – cena Feb 20 '20 at 14:28
Indeed this way is less confusing and simple than mine. Upvoted – Foxfire And Burns And Burns Feb 20 '20 at 14:31
@FoxfireAndBurnsAndBurns - but your approach is more efficient. – BigBen Feb 20 '20 at 14:32
@BigBen why inneficent? – Foxfire And Burns And Burns Feb 20 '20 at 14:33
Because it deletes multiple times - you delete only once. – BigBen Feb 20 '20 at 14:34
@BigBen Oh, I see your point now. Thanks :) – Foxfire And Burns And Burns Feb 20 '20 at 14:37

Foxfire And Burns And Burns · Answer 2 · 2020-02-20T14:35:34.100

You can union all ranges in a single range and then delete all at once:

Sub CLEAR_ZERO_ROWS()

Dim BigRange As Range
Dim MyArray As Variant
Dim i As Long

With ThisWorkbook.Sheets(1)
    MyArray = .Range("B2:D" & .Range("A" & .Rows.Count).End(xlUp).Row).Value 'values into array


    For i = 1 To UBound(MyArray) Step 1
        If MyArray(i, 1) + MyArray(i, 2) + MyArray(i, 3) = 0 Then
            If BigRange Is Nothing Then
                Set BigRange = .Range("A" & i + 1 & ":D" & i + 1)
            Else
                Set BigRange = Union(BigRange, .Range("A" & i + 1 & ":D" & i + 1))
            End If
        End If
    Next i

End With


'we delete all at once is bigrange is not nothing

If Not BigRange Is Nothing Then BigRange.Delete xlUp

Erase MyArray
Set BigRange = Nothing

End Sub

By the way, the code is adapted to a fake dataset I did on columns A:D, so you need to adapt that part. Before executing code I had:

After code I got:

Note that in both cases, the consecutive numbers in column F have not been deleted. The code just deletes the 4 cells of data and shifts up.

How to delete specific columns(not entirely) in excel vba

2 Answers2