VBA clear contents (number and date formats) but keep format

albertan

New Member
Joined
Nov 16, 2014
Messages
34
Hello,
I appreciate if somebody please help me with how it's better to clear contents but preserve formatting.

I have a data with dates and numbers. When I use Sheet.Range ("A2").CurrentRegion.Delete this preserves formatting for numbers but does not preserve for dates.
When I use Sheet.Range ("A2").CurrentRegion.ClearContents then it preserves formatting for Dates but not for numbers.
Is there any other way to clear it so that I keep formatting for both dates and numbers?

Thanks
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,426
Office Version
2010
Platform
Windows
Try doing it this way...
Code:
Sheet.Range("A2").CurrentRegion.Value = ""
 
Last edited:

albertan

New Member
Joined
Nov 16, 2014
Messages
34
Thank you. It only works for date format ranges but does not preserve format for numbered fields.

At this moment (as a temporary workaround) I'm just putting this code at the end of VBA to get back to number format:

Columns("L:R").NumberFormat = "General"
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,426
Office Version
2010
Platform
Windows
Thank you. It only works for date format ranges but does not preserve format for numbered fields.
It does for me. If the date format was currency for a cell before assign "" to it and I then enter a number back into the cell, that number gets formatted as currency.... if the cell was formatted as a date before I assigned "" to it, then put a number (say 45678) into the cell, that number would be displayed with the date format of the previous date entry.



Columns("L:R").NumberFormat = "General"
I thought this is not what you wanted???
 

albertan

New Member
Joined
Nov 16, 2014
Messages
34
Hi Rick, your code works for all columns that are formatted in Date and Text format but not for columns L through R which are formatted in number format.

That's why I had to put the code at the end to reformat columns L through R in a number format. (i.e. olumns("L:R").NumberFormat = "General")

 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,426
Office Version
2010
Platform
Windows
....but not for columns L through R which are formatted in number format.
What does happen for them? For example, if cell L2 is custom formatted as "$0.00" and set its value equal to "", then go back and enter 98.76, what displays in the cell?
 

albertan

New Member
Joined
Nov 16, 2014
Messages
34
for columns L through R for some reason the cells are showing 1/1/1900 in format, which looks like a date format.

Even if I change these columns to "General" format and run the code, it changes it back to 1/1/1900 format.

In my code I'm bringing the data from the SQL server. I use SQL string and the source data is in a number format. But perhaps because I'm using a string in my SQL statement, the data is being imported in a text format and it overwrites the previous formatted data.

Thanks
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,426
Office Version
2010
Platform
Windows
In my code I'm bringing the data from the SQL server. I use SQL string and the source data is in a number format. But perhaps because I'm using a string in my SQL statement, the data is being imported in a text format and it overwrites the previous formatted data.
I don't work with SQL, but as far as I know, importing data is the same as entering data and the cell adopts the format of the data being entered.
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top