I have many worksheets with sequential linear xy data that vary in length. I am looking for vba automation to delete (rows up) all data with the exception of x data (A) that is divisible by 50
My macro produces a helper column that flags the data appropriately through formula. The Autofilter selects the the flags and deletes. I would like to make this dynamic and be able to change the divisor to 100 or 200 through the code. Here's what my macro looks like.
Code
ub Divis50()
'
'
Sheets("VERT SCALES").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = _
"=IF((OR((RIGHT(RC[-2],2)=""50""),(RIGHT(RC[-2],2)=""00""))),""YES"",""NO"")"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C6062")
'sort filtered results
Range("C2").Select
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Add Key:=Range("C2") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VERT SCALES").Sort
.SetRange Range("A2:C6062")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' scroll to first no and delete rows
Rows("123:123").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
'sort "A" back to consecutive numbers
Range("A2").Select
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("VERT SCALES").Sort.SortFields.Add Key:=Range("A2") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("VERT SCALES").Sort
.SetRange Range("A2:C122")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
'delete filtered column
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
End Sub
Display More