Delete rows based on the value in a particular column

[manojrf]

Hi there,

The macro given below is one that I got from this forum to delete rows based on the value given in a particular column.

Dim LastRow&, FilterRange As Range
With Sheets("File")
.AutoFilterMode = False
LastRow = .Cells.Find("*", after:=.Range("B1"), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
Set FilterRange = .Range("F1:F" & LastRow)
FilterRange.AutoFilter Field:=1, Criteria1:="=2"
On Error Resume Next
FilterRange.SpecialCells(12).EntireRow.Delete
Err.Clear
.AutoFilterMode = False
End With
Set FilterRange = Nothing
Application.ScreenUpdating = True

It is meant to delete the entire rows (several rows) if the value in Column F in the corresponding row is 2. It works fine and fast. But the first row (first row only), even if the value in Column F is not 2, gets deleted.

I am not at all an expert in making macros. Can anyone help me to get it solved ?

Thanks

 

[DanteAmor]

Try this:

Sub deletelines()
  Dim LastRow&, FilterRange As Range
  With Sheets("File")
    .AutoFilterMode = False
    LastRow = .Range("F" & Rows.Count).End(3).Row
    Set FilterRange = .Range("F1:F" & LastRow)
 
    FilterRange.AutoFilter Field:=1, Criteria1:="=2"
    On Error Resume Next
    FilterRange.Offset(1).SpecialCells(12).EntireRow.Delete
    Err.Clear
 
    .AutoFilterMode = False
  End With
  Set FilterRange = Nothing
  Application.ScreenUpdating = True
End Sub

---------------------------------​

Here another option to try:

Sub deletelines_v2()
  Application.ScreenUpdating = False
  With Sheets("File").Range("F:F")
    .Replace what:="2", replacement:="#N/A", Lookat:=xlWhole
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[manojrf]

Try this:

Sub deletelines()
  Dim LastRow&, FilterRange As Range
  With Sheets("File")
    .AutoFilterMode = False
    LastRow = .Range("F" & Rows.Count).End(3).Row
    Set FilterRange = .Range("F1:F" & LastRow)
 
    FilterRange.AutoFilter Field:=1, Criteria1:="=2"
    On Error Resume Next
    FilterRange.Offset(1).SpecialCells(12).EntireRow.Delete
    Err.Clear
 
    .AutoFilterMode = False
  End With
  Set FilterRange = Nothing
  Application.ScreenUpdating = True
End Sub

---------------------------------​

Here another option to try:

Sub deletelines_v2()
  Application.ScreenUpdating = False
  With Sheets("File").Range("F:F")
    .Replace what:="2", replacement:="#N/A", Lookat:=xlWhole
    On Error Resume Next
    .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub


[/QUOTE]

Thanks for the quick reply.
 There is a problem with the first macro. Now the first row doesn't get deleted, whatever the value in Column F is. Even if the value in F 1 is 2, it is not deleted.

 

[DanteAmor]

There is a problem with the first macro. Now the first row doesn't get deleted

The first macro considers row 1 to be headers.
Better use the second macro.

 

[manojrf]

The first macro considers row 1 to be headers.
Better use the second macro.

Oh. I made the first row as the header and it works fine. Thanks.
The second one is showing "Compile error - Syntax error " on the third line (. Replace what..........)

 

[DanteAmor]

Oh. I made the first row as the header and it works fine. Thanks.

So if you are going to use a header in row one we go with macro 1.

The second macro works for me, maybe some problem in your data or version.

The important thing is that you already have a solution.

 

[manojrf]

So if you are going to use a header in row one we go with macro 1.

The second macro works for me, maybe some problem in your data or version.

The important thing is that you already have a solution.

The first one is working fine and so I have a solution.
Thanks a lot for spending your precious time for me . God bless.