Macro to Quickly Delete Rows Based on Cell Value in Column

[JHCali]

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.

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.

'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

 

[JHCali]

Hi Fluff,

When I run that code I get the error Cannot Use Command on Overlapping Sections.

Regards,
JHCali

 

[Fluff]

Do you have hidden columns?

 

[JHCali]

Hi Fluff,

Yes I do have columns that are grouped. Should I unhide first before running the macro and then re-hide?

Regards,
JHCali

 

[JHCali]

Hi Fluff,

I tried unhiding the columns and then running the macro. But it deletes all the rows of data.

Thank you for continuing to follow up with this.

Regards,
JHCali

 

[Fluff]

Do you have any merged cells?

 

[JHCali]

Do you have any merged cells?

Hi Fluff,

I do not have any merged cells. I'm at a loss. Your code seems so easy to understand.

Regards,
JHCali

 

[Fluff]

Would you be willing to upload your workbook to somewhere like OneDrive, GoogleDrive & post the link here?

 

[JHCali]

Would you be willing to upload your workbook to somewhere like OneDrive, GoogleDrive & post the link here?

Hi Fluff,

Sorry for the (extreme) delay. Below is a link to the workbook. All the data has been scrubbed. Please note there are columns with blank information just as there is with the real data.

https://drive.google.com/open?id=1qu_ubcc85rz3MCeGukEzSO5w1mRGBq6z

Thank you very much in advance for your help.

Regards,
JHCali

 

[Fluff]

Thanks for the file.
Try

   With ActiveSheet
      .Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
      .Range("AU:AU").Replace "<Not Applicable>", "Not Applicable", xlWhole, , False, , False, False
      If .FilterMode Then .ShowAllData
      .Range("A4:AV4").AutoFilter 47, "Not Applicable"
      .AutoFilter.Range.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .ShowAllData
   End With

 

[JHCali]

Thanks for the file.
Try

   With ActiveSheet
      .Outline.ShowLevels RowLevels:=0, ColumnLevels:=2
      .Range("AU:AU").Replace "<not applicable="">", "Not Applicable", xlWhole, , False, , False, False
      If .FilterMode Then .ShowAllData
      .Range("A4:AV4").AutoFilter 47, "Not Applicable"
      .AutoFilter.Range.Offset(1).SpecialCells(xlVisible).EntireRow.Delete
      .ShowAllData
   End With

Hi Fluff,

I completely forgot to follow-up with you on this. The code worked.

I'm very grateful for your help. It's so quick and easy now to eliminate unnecessary rows of data from a mass export from our financial system.

Regards,
JHCali</not>