Problem when remove blank cells from a range

[espenskeie]

Hello

I have a range where some of the rows are blank, and sometimes there are two or more in a row without data.

The code that I have remove one blank cell range, but it also skip the next row because the loop doesn't "know" that one row has been removed.

I also have to add that I don't want the entire row to be deleted, only the range .

Sub Delete_Row()
    
    Dim temp As Long, x As Long

temp = Sheets("1HourDATA").Range("C65536").End(xlUp).Row

For x = 2 To temp
Cells(x, 3).Select
If Cells(x, 3) = "" Or Cells(x, 3) = 0 Then
Range("C" & x & ":L" & x).Select
Selection.Delete Shift:=xlUp
End If
Next x
End Sub

Regards
Espen

 

[Jonmo1]

You have to go backwards (bottom to top) when deleting rows

Change
For x = 2 To temp
to
For x = temp to 2 Step -1


Same applies to deleting columns (you would go Right to Left)

 

[VoG]

You need to loop backwards

Sub Delete_Row()
    
    Dim temp As Long, x As Long

temp = Sheets("1HourDATA").Range("C65536").End(xlUp).Row

For x = temp To 2 Step -1
    If Cells(x, 3) = "" Or Cells(x, 3) = 0 Then
        Range("C" & x & ":L" & x).Delete Shift:=xlUp
    End If
Next x
End Sub

 

[espenskeie]

Thanks guys!!!! That was fast.

Great minds think alike they say