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.
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.
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
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