Delete on Search macro needs to run on used range of column

[farrukh]

Hi All,

I have a code which works fine , but i have a issue when i want to delete rows 5000 or more the macro takes lot of time to run . I need that macro starts from line to as mention below and go till used range of the column and then delete the row according to given column c?

Sub deleteOnSearch()
J = 2

For i = 2 To 5000 'give the no of rows to search for

Range("C" & J).Select 'give here the column to search in

If ((ActiveCell.Value() = "0")) Then 'give here the search string

Rows(J & ":" & J).Select

Selection.Delete Shift:=xlUp

J = J - 1

End If

J = J + 1

Next

End Sub

Cheers

[StephenR]

Do you have any blank rows in your data?

[farrukh]

Yes i have the blank row in data as well i need to remove blank row based on column C as well.


Cheers

[StephenR]

This might be quicker:

Sub deleteOnSearch()

Dim i As Long, r As Range

Application.ScreenUpdating = False

For i = 2 To Range("A" & Rows.Count).End(xlUp).Row 'give the no of rows to search for
    If Range("C" & i).Value = 0 Then 'give here the search string
        If r Is Nothing Then
            Set r = Range("C" & i)
        Else
            Set r = Union(r, Range("C" & i))
        End If
    End If
Next i

r.EntireRow.Delete shift:=xlUp

Application.ScreenUpdating = True

End Sub

[farrukh]

HI StephenR.

Forum Guru :-) That is working perfectly... Much Much quicker ...

Cheers
Farrukh