HELP PLEASE with Macro: delete row if value in row D is below "x" or above "y"

[paul.a.t.krueger]

The title says it all, I've been trying for two days now, both using the record function/try to write it myself (didn't work at all) or alter code of others who had written macros with similar tasks at hand (kept popping out error messages).

Any help would be highly appreciated!

Regards,

Paul

PS.: it should leave out the first 20-25 rows as these are not part of the actual table.

[#2 · Accepted solution]

The most efficient way I know to delete rows using VBA is to use auto-filter feature

You can place this code into a module and try to change the sheet names range and variables x , y to the one you have. 

I wrote this code for the example I have attached. 

you can see that it runs the code on Sheet1 and the range is set after the 25th row, it will ignore the previous rows up to 25th row. ws.Range("A25:O" & lastRow)  also you can change your column from O to whatever your column is

you can assign values to x and y in the code.

if you download the workbook and you run the code in it. you will see what it will do to the data.

Please remember when running any macro, run it on a copy of workbook meaning (you must have a backup copy) because you cannot undo a macro action,  just in case if you click on save by accident.

Sub Test()

Dim x As Double, y As Double
      x = 7
      y = 4
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A25:O" & lastRow)
    With rng
    .AutoFilter Field:=4, Criteria1:="<" & x, Operator:=xlAnd, Criteria2:=">" & y
      .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ws.AutoFilterMode = False
End Sub

[#3]

Hi @paul.a.t.krueger,

I can't get you. Please put screenshot of your file. Coz specifying 'D' as row, But it is a column. Row should be in numbers only.

What is it mean below 'x' or above 'y'? I know it only for numbers, but not alphabets (i.e. below 100 or above 150)

[#4]

Thanks a lot Jamil, I will try running it on Tuesday when I am back in the office. Really appreciate your help, happy Easter!

[#5]

Hi Logaraj,
Thanks for the reply!

You are right, I was talking about a column.

X or Y where meant to be variables for numerical values.

I'll try running Jamils code on Tuesday and let you guys know how ir worked out,

[#6]

You are welcome. I am sure the macro will work for you. post back if you have any issue with it.

Happy Holiday!

[#7]

--> .AutoFilter Field:=4, Criteria1:="<" & x, Operator:=xlAnd, Criteria2:=">" & y

Pops out runtime error 1004: Autofilter method of range object couldnt be executed.

[#8]

perhaps your data could have different number of columns. can you please post a screenshot of your sample data? Field 4 is the forth column, so make sure that the range you are running this code on matches, otherwise you could change the Field 4 to whatever column which your actual data is.

[#9]

[#10]

in this particular case, up to row 27 the data shouldn't be touched by the macro, after it should delete rows if value in column c is below 500 or above 2000.

[#11]

then try

Sub Test()

Dim x As Double, y As Double
 x = 500
 y = 2000
 Dim ws As Worksheet
 Dim rng As Range
 Dim lastRow As Long

 Set ws = ActiveWorkbook.Sheets("Sheet1")

 lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

 Set rng = ws.Range("A27:C" & lastRow)
 With rng
 .AutoFilter Field:=3, Criteria1:="<" & x, Operator:=xlOr, Criteria2:=">" & y
 .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
 End With

 ws.AutoFilterMode = False
End Sub

[#12]

Works perfectly, thank a lot! MVP indeed 

[#13]

You are welcome. Thanks for the feedback.