Modify and Enhance My Recorded Code - Delete Rows Based on Value

Myproblem

Board Regular
Joined
May 24, 2010
Messages
198
I searched many sites and used codes which delete Rows based on criteria. In my case those codes works, BUT it took so much time about 30 min since there is about 75 thsd rows, and that solution in not time saver.
I recorded code below and it is done in seconds. I need help to make it dynamic, VBA, since number of rows is every month larger.
Basicly, I need code which delete rows based on Column B, where walue is "R"
thx in advance

Code:
Sub DelRowsBasedOnOneCriteria()
'
' DelRowsBasedOnOneCriteria Macro
'
'
    ActiveSheet.Range("$A$1:$R$74478").AutoFilter Field:=2, Criteria1:="R"
    Rows("8733:8733").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("B1").Select
    ActiveSheet.ShowAllData
    Cells.Select
    ActiveWorkbook.Worksheets("MySheet").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("MySheet").Sort.SortFields.Add Key:=Range( _
        "A2:A74478"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("MySheet").Sort
        .SetRange Range("A1:R74478")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B4").Select
End Sub
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
You could use the much shorter and more optimized code below:

Code:
Sub DelRowsBasedOnOneCriteria()'
' DelRowsBasedOnOneCriteria Macro
'
    Application.ScreenUpdating = False
    With Range("A1").CurrentRegion
        .AutoFilter Field:=2, Criteria1:="R"
        .Offset(1).SpecialCells(xlCellTypeVisible).ClearContents
        .AutoFilter
        .Sort .Cells(1, 1), xlAscending, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub
 

fredlo2008

Active Member
Joined
Jan 12, 2012
Messages
254
Hi,

This is a difference approach. See if it works for you. Always try it in a copy of your workbook.

Code:
Sub DelRowsBasedOnOneCriteria()

Dim lRow As Long


Application.ScreenUpdating = False


With Sheets("MySheet")
    ' assign a variable to the last row number in column "B"
    lRow = .Cells(Rows.Count, "B").End(xlUp).Row
    
    With .Range("B1:B" & lRow)
        'Replace all values "R" in column "B" with nothing
        .Replace What:="R", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows
        'delete all cells with nothing and an error handler in case there is no blank cells
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        On Error GoTo 0
    End With
End With


Range("A1").Select
        
Application.ScreenUpdating = True


End Sub
 
Last edited:

Myproblem

Board Regular
Joined
May 24, 2010
Messages
198
@ fredlo 2008
your proposed solution is much more consuming than wigi´s one above
thx again
 

Watch MrExcel Video

Forum statistics

Threads
1,122,778
Messages
5,598,027
Members
414,204
Latest member
Excelmee

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
Top