1

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. enter image description here

so eventually i want it to be

enter image description here

Improve this question
6
  • 2
    If you delete a row, the row below it then takes that row number, so the code will skip it. That could be why? Commented Feb 20, 2020 at 14:11
  • 2
    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. Commented Feb 20, 2020 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. Commented Feb 20, 2020 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. Commented Feb 20, 2020 at 14:23
  • @ Pᴇʜ yeah i did search but i missed out the part on For j = lr To 1 Step -1. Thanks anyways Commented Feb 20, 2020 at 14:24

2 Answers 2

Reset to default
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
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

is it possible not to delete the entirerow but just from column T to V?
@cena do you really mean "delete" (remove the cells) or do you mean "clear" (remove the content)?
@Pᴇʜ i think shifting the cells once it is being deleted means remove the cells?(not sure about that)
@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?
Try: .Range("T" & i & ":V" & i).Delete Shift:=xlUp
|
2

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:

enter image description here

After code I got:

enter image description here

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.