Format column in excel with vba to date format

Question

I am trying to reset the formatting of my excel sheet, the problem is that I have 4 columns which should be date format. How can I find all columns which contain "DATE" in header (Such as : last machined date, assembly date, order date etc..) and change this format to date? Note: Needs to be dynamically because it might change from C:C to E:E in the future or more columns added.

Sub formatTable(ws As Worksheet)
    On Error Resume Next
    Dim lo As ListObject
    Set lo = ws.ListObjects("FilterParts")

    'Format the table
    ws.UsedRange.Font.Bold = False
    ws.UsedRange.Style = "Normal"

    lo.TableStyle = "TableStyleMedium9"
    'Format every column that has "DATE" in its header to a date column
    'ws.Range("C:C").NumberFormat = "dd/mm/yyyy" and so on


End Sub

What exactly is in the header row? Do they all contain the word "date"? Or perhaps all dates in the header row contain "/"? — user6028892
– user6028892, Commented Apr 14, 2016 at 8:48
Header is located in row 2, The header row looks like this(sperated by ,) ID,LAST MACHINED DATE,DRAWING NUMBER, ESTIMATED HOUR,PDF,TXT ..... — skatun
– skatun, Commented Apr 14, 2016 at 9:06

Taosique · Accepted Answer · 2016-04-14 09:47:51Z

3

Just iterate through your columns like this, check if their names contain "Date" and if yes, then format them:

Set lo = ws.ListObjects("FilterParts")
For Each dataColumn In lo.ListColumns
    If InStr(dataColumn.Name, "Date") > 0 Then
        dataColumn.DataBodyRange.NumberFormat = "dd/mm/yyyy"
    End If
Next dataColumn

Run this macro every time you add a new column.

edited Apr 14, 2016 at 9:47

answered Apr 14, 2016 at 9:42

Taosique

7,9143 gold badges33 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2016-04-15 02:04:27Z

1

A longer coding option but uses Find to avoid looping through the range.

Dim ws As Worksheet
Dim lo As ListObject
Dim rng1 As Range
Dim StrAddress As String

Set ws = ActiveSheet
Set lo = ws.ListObjects("FilterParts")

Set rng1 = lo.Range.Rows(1).Find("Date", , , xlPart)

If Not rng1 Is Nothing Then
    StrAddress = rng1.Address
    rng1.Offset(1, 0).Resize(lo.ListRows.Count, 1).NumberFormat = "dd/mm/yyyy"
    Do
    Set rng1 = lo.Range.Rows(1).Find("Date", rng1, , xlPart)
    rng1.Offset(1, 0).Resize(lo.ListRows.Count, 1).NumberFormat = "dd/mm/yyyy"
    Loop While StrAddress <> rng1.Address
End If

answered Apr 15, 2016 at 2:04

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Comments

Paul S · Accepted Answer · 2016-04-14 09:47:21Z

0

Dim HdrRow as range
Dim Cl as Range
Set HdrRow = ActiveSheet.UsedRange
Set HdrRow = HdrRow.Row(1) 'assuming row 1 of the data contains headers

For Each Cl In HdrRow.cells
    If Instr(lCase(Cl.Value), "date") > 0 then 'This column has "date" in the header text
enter code here
Next Cl

from here you can either store the cell/column number for a loop later or loop the cells in this column right away....

this should get you started just post back if you need more help.

answered Apr 14, 2016 at 9:47

Paul S

1901 gold badge1 silver badge11 bronze badges

Comments

m00am · Accepted Answer · 2018-01-26 09:41:17Z

-2

Try:

Range("A1","A50000").NumberFormat = "dd\/mm\/yyyy"

edited Jan 26, 2018 at 9:41

m00am

6,31612 gold badges60 silver badges74 bronze badges

answered Jan 26, 2018 at 9:11

sorana

1

Collectives™ on Stack Overflow

Format column in excel with vba to date format

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related