Delete rows based on the value in a particular column

manojrf

Board Regular
Joined
Mar 28, 2011
Messages
109
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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Try this:

VBA Code:
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:
VBA Code:
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
--------------​
 
Last edited:
Upvote 0
Try this:

VBA Code:
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:
VBA Code:
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.
 
Upvote 0
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..........)
 
Upvote 0
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. ;)
 
Upvote 1
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,208
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top