Microsoft Office Forums - View Single Post - Need Help Creating a Macro to Delete Date and Number Information

Philb1 · 02-25-2016, 07:29 PM

Hi
I don't know how you have the sheet set out so I have separated the routine for columns A & B.
Change the TopRowA & TopRowB number to where you want the routine to start from & change the ThisWorkbook.Sheets(1) red number to the sheet number you want to run it on.
I think it does what you're asking for.

Cheers

Code:

Option Explicit

Sub FormatFindnDelete()

Dim Wsht As Worksheet
Dim rCell As Range
Dim TopRowA As Long, TopRowB As Long

Set Wsht = ThisWorkbook.Sheets(1) ' Change this number to whatever sheet you want to run it on
TopRowA = 4 ' Change this number to suit the top row of column A
TopRowB = 4 ' Change this number to suit the top row of column B

With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

'   Column A
For Each rCell In Wsht.Range(Wsht.Cells(TopRowA, 1), Wsht.Cells(Rows.Count, 1).End(xlUp))

    If Wsht.Cells(rCell.Row, 1).NumberFormat = "mm/dd/yyyy" Then Wsht.Cells(rCell.Row, 1).Clear

Next rCell

'   Column B
For Each rCell In Wsht.Range(Wsht.Cells(TopRowB, 2), Wsht.Cells(Rows.Count, 2).End(xlUp))

    If Wsht.Cells(rCell.Row, 2).NumberFormat = "#,##0" Then Wsht.Cells(rCell.Row, 2).Clear

Next rCell

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

Exit Sub

End Sub