Need Help Creating a Macro to Delete Date and Number Information

rsrasc · #1 02-25-2016, 09:27 AM

Hi all,

I have the following date format in Column A:

10/19/2015

and in Column B I have the following number format:

119,702

I have been searching the internet for a macro so I can delete just the information for these two formats for Column A and B, but I have not been successful.

My file has over 7,000 rows of information.

Thank you in advance for your help.

Cheers!

Philb1 · #2 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

rsrasc · #3 02-26-2016, 04:50 AM

Thank you so much for the code. The code pertaining for Column B to remove the numbers in the following format (111,356) is working as intended.

The code to remove or delete (just the info) for the date format in Column A is not working for me.

I also tried you excel attachment, run the code also, but didn't work either, mean, it only delete a couple of dates.

Thanks again for the code!

Cheers!

Philb1 · #4 02-26-2016, 12:55 PM

Hi
The code looks for a cell with mm/dd/yyyy format & if it finds one in column A, it will delete everything in the cell. If it's formatted as something other than mm/dd/yyyy, it ignores it. The dates on the attachment I posted are mainly dd/mm/yyyy, so when the code is run it deletes the few cells formatted as mm/dd/yyyy & leaves the rest untouched

If you're having a problem attach an example workbook so I can see whats what

Cheers

rsrasc · #5 02-27-2016, 03:12 AM

Hi PhilB1,

Thank you for answering my questions.

Here is a copy of the file. I didn't notice (my mistake) it but the date format is

10/15/15

instead of

10/15/2015

I fix it in the macro but still does not work.

Thanks for your help.

I appreciate it.

Cheers!

Philb1 · #6 02-27-2016, 09:27 PM

I had a look at the sample you sent, it was all formatted as dd/mm/yyyy, maybe excel does that on opening (My date format is dd/mm/yyyy). I changed the code to test for mm/dd/yyyy and mm/dd/yy, tested it and all seems ok here. You may have to change some of the cells to mm/dd/yyyy or mm/dd/yy manually. That American format causes a lot of problems doesn't it.
I added .Interior.Color = vbRed to the code on front of the clear command for the dates in column A and .Interior.Color = vbYellow for column B. There's a button on sheet 2 to test the code. If it finds either or the above date formats, the cells change to red, if it finds the 555,555 number format, the cells turn yellow.
I've left it as it shows on my computer after the macro has run. Change the cell fill colours to no colour & try it. The columns C & D are in the same format for easy copy/paste when testing. Delete the .Interior.Color = vbYellow & the same for the red and delete the ' before ' before .Clear to test it clearing the cells.

Cheers

rsrasc · #7 02-28-2016, 05:12 AM

Hi PhilB1,

Thanks again for the info provided. I was able to change the date format using the below code. After that I ran your color code macro and is highlighting the date format
in red. So with your other macro I was able to delete the date info in column A.

Code:

Sub ConvertingDateFormat()
'
' Macro3 Macro
'

'
    Columns("A:A").Select
    Selection.NumberFormat = "mm/dd/yyyy"
    Range("A1").Select
End Sub

Therefore, I'm happy with the outcome.

Thanks again for taking your time helping me on this.

Cheers!

Philb1 · #8 02-28-2016, 04:27 PM

Looking at you last piece of code I see you'll be formatting column A to mm/dd/yyyy, if you do that before you run my code, it'll delete the lot on column A. But you know what you want.

Glad I helped you get to the desired outcome.

rsrasc · #9 02-29-2016, 06:10 AM

You are right, it is deleting everything in Column A. I also noticed that but I'm OK with that.

Anyway, both of your codes helped me to do some other things that I want to implement later.

Thanks again!

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating a Template using a Macro to fill in various information	brojames	Word	7	11-09-2015 02:51 PM
Creating a Document that Pushes Information to other Word Documents	jbwhisler	Word	2	08-25-2015 12:12 AM
How to find number of coma and then add that number of rows in word using macro?	PRA007	Word VBA	7	05-27-2015 10:45 PM
Delete starting number *) and delete Unneeded data in series	frustrated teacher	Word VBA	5	12-07-2014 06:53 PM
How to Automatically Add/Delete Columns Based On Information	kconnolly	Excel	7	04-08-2014 07:23 AM