Link to home
Start Free TrialLog in
Avatar of Massimo Scola
Massimo ScolaFlag 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
Avatar of Rgonzo1971
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
Avatar of Shums Faruk
Shums Faruk
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Massimo Scola

ASKER

Thanks guys ..the codes work like a charm.
You're Welcome Massimo! We're glad we're able to help
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?
Do you want answer here or you gonna raise a new question?