Macro to Quickly Delete Rows Based on Cell Value in Column

JHCali

New Member
Joined
Dec 10, 2008
Messages
29
Hi All,

I have a database with about 48 columns and over 50,000 rows of data. The column headers are on row 4.

Below is a code that I recorded for copy-pasting over certain cells with values and sorting the data by Column AU, which is Cost Center.

Code:
Sub DeleteRowfromTransactionData()

'Copy Paste Values


Columns("AU:AV").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


'Sort Data by Cost Center


ActiveWorkbook.Worksheets("Transaction Detail - Import").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Transaction Detail - Import").AutoFilter.Sort. _
        SortFields.Add Key:=Range("AU4"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Transaction Detail - Import").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
        
    End With

I'm trying to add to this a code that will delete all rows for which the Cost Center in Column AU is "<Not Applicable>". I recorded a code where I (i) filter the data and deselect everything except "<Not Applicable>", (ii) delete all rows where Column AU is "<Not Applicable>", and (iii) undo the filter. That code is below.

Code:
'Delete Rows

Range("AU4").Select
    ActiveSheet.Range("$A$4:$AV$48761").AutoFilter Field:=47, Criteria1:= _
        "<Not Applicable>"
    Rows("5:5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$4:$AV$4681").AutoFilter Field:=47
    Range("AU5").Select
End Sub

However, as you can see above, the range is not dynamic.

Appreciate any help in advance on how to quickly delete rows based on the above specified criteria for a large data set using dynamic ranges.

Regards,
JHCali
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Does col AU hold formulas that return "" or are those cells simply empty?
 
Upvote 0
No formulas. I run a macro that copy pastes values over the formulas. No “” values in that column either.
See if this single line of code does what you want...

Columns("AU").SpecialCells(xlBlanks).EntireRow.Delete
 
Upvote 0
See if this single line of code does what you want...

Columns("AU").SpecialCells(xlBlanks).EntireRow.Delete

Hi Rick,

I’m not looking to delete rows where Column AU is blank. I want to delete rows where the value in AU is “<Not Applicable>”.

Regards,
Junaid
 
Upvote 0
Hi Rick,

I’m not looking to delete rows where Column AU is blank. I want to delete rows where the value in AU is “<not applicable="">”.

Regards,
Junaid

Hi Rick,

Sorry. I meant to see delete rows where Column AU is “Not Applicable.” I was typing that all along but it was showing up as blanks. My apologies.</not>
 
Last edited:
Upvote 0
something like this should gt you on the way
Code:
'Filter out "".                                                          'column AU
    ActiveSheet.Range("$A$1:$Y$15000").AutoFilter Field:=37, Criteria1:=Array( _
    """.", ), Operator:= _
    xlFilterValues
'                The last column on sheet
    Range("A2:BB15000").SpecialCells(xlCellTypeVisible).EntireRow.Delete  'This delets the range
    ActiveSheet.ShowAllData

paul
 
Last edited:
Upvote 0
How about
Code:
'Delete Rows
   With ActiveSheet
      .Range("A4:AV4").AutoFilter 47, "Not Applicable"
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilterMode = False
   End With
 
Upvote 0
Thanks everyone for their suggestions.

However, none of them seem to be working quite right. The macros seem to be deleting rows with values in Column AU other than "Not Applicable".

It might just have to be one of those things I do manually and it costs me a few extra seconds.

Regards,
JHCali
 
Upvote 0
What about
Code:
'Delete Rows
   With ActiveSheet
      .Range("A4:AV4").AutoFilter 47, "Not Applicable"
      .AutoFilter.Range.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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