CODEWITHSUNDEEP
vbaexceldate
skatun
skatun

Reputation: 877

Format column in excel with vba to date format

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

Upvotes: 1

Views: 25551

Answers (4)

sorana
sorana

Reputation: 1

Try: 

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

Upvotes: -2

brettdj
brettdj

Reputation: 55692

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

Upvotes: 1

ttaaoossuu
ttaaoossuu

Reputation: 7894

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.

Upvotes: 3

Paul S
Paul S

Reputation: 190

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.

Upvotes: 0

Related Questions