Applying NumberFormat Date to entire column where header equals “Date” in Excel VBA

1
votes

I wanted to Apply a TableStyle to an imported Table, but apparently a TableStyle can't overwrite currently existing Formats. But when I use .ClearFormats, it will also Clear the NumberFormat for the "Date" column.

So I decided to do a workaround and use .ClearFormats, but re-apply the Date NumberFormat afterwards. The only problem I am facing is the fact that there are rows with an empty cell in the "Date" column, which makes me unable to use .End(xlDown).

So over to the question: How can I select a Range/Selection where the header name equals "Date" and apply the NumberFormat "dd-mm-yyyy" to the entire column. (PS. I can't hardcode the column number, because the Date might be in a different column for the next imported sheet.)

2
vbaexceldate
If you use Table feature, you can use ListObject(index).ListColumns(index) to refer to the column. Or you can use .End(xlUp) from the bottom of the sheet. – kolcinx

2 Answers

2
votes
colDate = Application.Match("Date", Rows(1), 0) '<-- Assuming your header is at row 1
Columns(colDate).NumberFormat = "dd-mm-yyyy"

The first statement fetches the label "Date" in the header row. The second sets a Date format to the used part of that column.

0
votes

to strictly answer your question:

With Rows(1).Find(what:="Date", LookIn:=xlValues, lookat:=xlWhole) '<--| change "1" to your actual header row index
    .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.count, .Column).End(xlUp)).NumberFormat = "dd-mm-yyyy"
End With

while if you don't want to rely on ActiveSheet:

With Worksheets("myTableWorksheetName").Rows(1).Find(what:="Date", LookIn:=xlValues, lookat:=xlWhole) '<--| change "myTableWorksheetName" to your actual sheet name and "1" to your actual header row index
    .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.count, .Column).End(xlUp)).NumberFormat = "dd-mm-yyyy"
End With