VBA deleting rows based on one cell value

    I want to include macro that deletes whole row if number in B cel of that row is lower than 2000


    I came with something like this but it doesnt do anything basically, runs through 500 rows with no effect


    Dim x As Integer

    For x = 2 To 500
    If Range("B" & x).Value < 2000 Then
    Range("B" & x).Select
    Selection.EntireRow.Select
    Selection.Delete Shift:=xlUp
    Else
    End If
    Next

    All numbers are converted


    Can you guys help me with what I'am missing here ?

    Re: VBA deleting rows based on one cell value


    Hi


    One of the problems with this sort of approach is that people don't stand back programmatically and think how would I do this manually. Would you select every cell from rows 2 to 500 manually and delete each? No you would not. You would filter the data and remove the offending lines all at once.


    So too in code you would act in the same way.


    Code
    Sub GetRid()
    [b1:b500].AutoFilter 1, ">=2000"
    [b2:b500].EntireRow.Delete
    [b1].autofilter
End Sub


    Hopefully you get this working without a file - if not sing out and I will dump a working file in here. :)


    Take care


    Smallman

    Re: VBA deleting rows based on one cell value


    Well, I cant post a file till tommorow as im not in work today.

    I figured it out by the end of yesterday. My code was actually working but after deleting a row it didnt skip back one line so after adding x=x-1 it was all gucci.

    As for Smallman solution. It looks way simpler and Ill give it a try tommorow and post results.

    Thx for help.

    Re: VBA deleting rows based on one cell value


    I checked Smallman's solution and its is actually way better now.


    Works much faster, as expected :)


    Thx dude

    Edited once, last by Carim ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login