Hello all - I have the macro below where I recently added " so that I can remove any rows that do not start with DCQ or that are blank. The macro runs and deletes most of the rows where the cell is empty but it does not delete all of them. Please note that I added the LEN function to the workbook and found that some cells have 0 characters while others have 50. Any idea what I need to add to remove all rows that are blank with the addition of those that do not start with DCQ?
Code:
Sub Clean_Format() Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ActiveWorkbook.Sheets("Sheet1")
lastRow = ws.Range("M" & ws.Rows.Count).End(xlUp).Row
Set rng = ws.Range("M1:M" & lastRow)
With rng
.AutoFilter Field:=1, Criteria1:="<>DCQ*", Criteria1:="= "
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
End Sub
* Side question - if I want to format column A as Text and column DF as Time, where can I add that to the macro?