1

I have a problem with my code about delete blank rows. It just has to delete some rows not all blank rows and rows value "0". I don't wanna use .SpecialCells(xlCellTypeBlanks) as some threat on SO forum.

Dim R As Integer
R = Range("CuoiNKC").Row - 1
Dim DelCell As Range
Dim DelRange As Range
Set DelRange = Range("J9:J" & R)
For Each DelCell In DelRange
    If DelCell.Value = "0" Or DelCell.Formula = Space(0) Then
        DelCell.EntireRow.Delete
    End If
Next DelCel
Krish Munot
  • 1,093
  • 2
  • 18
  • 29
kobebryant
  • 71
  • 2
  • 2
  • 8

1 Answers1

2

Why don't you use Range AutoFilter Method instead of looping.
Assuming you have the correct value of DelRange in your code, try this:

DelRange.AutoFilter 1, AutoFilter 1, "=0", xlOr, "=" 'filtering 0 and space
DelRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete xlUp 'delete visible cells
ActiveSheet.AutoFilterMode = False 'remove auto filter mode

Btw, if you want to stick with your logic, you need to iterate the rows backward.
You can only do that using the conventional For Next Loop. Again assuming value of R is correct.

For i = R To 9 Step -1
    If Range("J" & i).Value = "0" Or Range("J" & i).Value = " " Then
        Range("J" & i).EntireRow.Delete xlUp
    End If
Next
L42
  • 19,427
  • 11
  • 44
  • 68
  • This way as we record macro, I know this way too. But in my code I dont know why It's just delete some rows and do it again it continues delete much more but not all. With my code I want to solve more problem @L42 – kobebryant Jan 19 '15 at 08:03
  • Thank you L42 and if I want to know how many row was deleted, how to do? (how many i was used) – kobebryant Jan 19 '15 at 08:16
  • @kobebryant Just add another counter variable inside the loop. – L42 Jan 19 '15 at 10:01