Avatar of Massimo Scola
Massimo Scola
Flag for Switzerland asked on

Excel VBA: Replace values in Listobject

I am importing some data with a MS QUERY from another workbook and the data is put into a table called CUSTOMERS.

The listobject has several columns and one of them is "First Order Date".
There is one value that I want to remove (not hide with a cell format): 00.01.1900  00:00:00

Is there a neat way to delete this value in the entire listobject with VBA?
I do not mind if the data is converted to a string if that makes it easier and the cell is empty

Thanks

Massimo
Microsoft ExcelVBAMicrosoft Office

Avatar of undefined
Last Comment
Shums Faruk

8/22/2022 - Mon
Rgonzo1971

Hi,

pls try
Sub Macro1()
Application.ScreenUpdating = False
For Each c In Range("Table1[First Order Date]")
    If c.Value = 0 Then
        c.ClearContents
    End If
Next
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
SOLUTION
Shums Faruk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rgonzo1971

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Massimo Scola

ASKER
Thanks guys ..the codes work like a charm.
Shums Faruk

You're Welcome Massimo! We're glad we're able to help
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Massimo Scola

ASKER
I do have one more follow up question though ..

What if I want to apply this code to every column of the listobject?
How would you amend the code?
Shums Faruk

Do you want answer here or you gonna raise a new question?