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

[albertan]

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]

Try doing it this way...

Sheet.Range("A2").CurrentRegion.Value = ""

 

[albertan]

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]

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]

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]

....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]

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]

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.