VBA Timestamp from multiple named columns using VBA

[stephenedger]

Hi all,

I have a table (tab= "(HIDDEN) Source Data") containing data in range C3:W2000 (though sometimes there are less than 2000 rows containing data - it varies). Columns L, N and O contain dates and timestamps. The data in the table is used in a couple of different pivot tables in the same workbook to analyse the original data. I am struggling to create a VBA which will remove the timestamps from the named columns. I appreciate I can highlight the rows and format them to only show the dates, but the formatting resets in the pivot tables everytime I refresh the data (which is once a week). I was hoping to create a simple code that would remove the timestamps when I run it. So far I have tried:

Sub ConvertDates()
'
' Converts date and time column to show date only

Dim Rng As Range

Dim WorkRng As Range
On Error Resume Next

Set WorkRng = Range("$L$4").End(xlDown).Select
Set WorkRng = Range("$N$4").End(xlDown).Select
Set WorkRng = Range("$O$4").End(xlDown).Select

For Each Rng In WorkRng
Rng.Value = VBA.Int(Rng.Value)
Next
WorkRng.NumberFormat = "mm/dd/yyyy"
End Sub

Can anyone point out the glaring error in my code. If I select $L$4;$L$2000 in the range, it ends up converting the rows that contain information to include a date, which is not ideal.

I appreciate your consideration.

regards,

Stephen

 

[stephenedger]

I gave up trying to figure this and created three separate codes to each perform on 1 column at a time.