VBA Macro that will clean and tranform messy time/hour workbook.

[PWil84]

Hello All,

I receive a work book weekly that's messy with time custom formatted, inconsistent formatting of time, and text in rows where time should be that I have to filter and delete. Is there a way I can write a macro in VBA that would filter and delete rows with text then format rows into proper time format? I've gotten started with a filter function in VBA can anyone provide guidenace about how I can do the rest?

Sub Delete_rows_based_values()
ActiveSheet.Range("A1:P1").AutoFilter Field:=8, _
Operator:=xlFilterValues, Criteria1:=Array("NO SHOW", "No Show", "no show")


End Sub

 

[Automatrix]

Power Query is literally built to clean up messy data sources, you should give it a go.