How to select column and display its current format using VBA Macro?

Question

Please find my requirement below for which I am unable to find any solution:

1. Iterate over workSheet from workbook
2. Find all the columns containing date values using current format/type of column
(Here is a trick. Worksheet is not static, it can contain any number of columns containing date values. Columns containing date values may have any name. And such worksheets can be more than one in number)
3. Apply macro on date columns for date formatting (below macro) if "Flag" value is "y"

<code>
Sub FormatDate()
    If wksSecDist.Range("Flag").value = "y" Then
        LastRowColA = Range("X" & Rows.Count).End(xlUp).Row
        ' Here I am finding total number of rows in column X
        wksSecDist.Range("X2", "X" & LastRowColA).NumberFormat = "dd/mmm/yyyy"
        ' Here applying specified date format to Range("X2", "X10") [if last row index for column X is 10]
    End If
End Sub
</code>

I am just a beginner to VBA.
Thanks in advance.

Gary Evans Gary Evans · Accepted Answer · 2016-05-26T10:35:26

I suspect you didn't find a solution on the internet because you looked simply for a solution and not the parts needed to build your own solution.

You mention you are a VBA beginner, please take the below answer to be of educational use and begin you in getting you where you need your tool to be. Note, if it doesn't answer your question because of information that was not included, it has still answered your question and the missing information should form part of a new question. That said, lets get this function up and running.

From what you have written I have interpreted the requirement to be: -

Look over all worksheets in a workbook ('worksheets can be more than one in number')
Check every column to see if it holds a date value
If it does, set the whole column to a specific format

What is needed to accomplish this is iteration(loops), one to loop through all worksheet, and another to loop through all columns: -

The is pseudo code of the target: -

.For each Worksheet in the Workbook

..For each Column in the Worksheet

...If the Column contains dates then format it as required

..Process next column

.Process next Worksheet

We achieve this using a variable to reference a Worksheet and using a loop (For Each) to change the reference. The same goes for the columns.

Public Sub Sample()
Dim WkSht       As Excel.Worksheet
Dim LngCols     As Long
Dim LngCol      As Long

'This loop will process the code inside it against every worksheet in this Workbook
For Each WkSht In ThisWorkbook.Worksheets

    'Go to the top right of the worksheet and then come in, this finds the last used column
    LngCols = WkSht.Range(WkSht.Cells(1, WkSht.Columns.Count).Address).End(xlToLeft).Column

    'This loop will process the code inside it against every column in the worksheet
    For LngCol = 1 To LngCols

        'If the first cell contains a date then we should format the column
        If IsDate(WkSht.Cells(2, LngCol)) Then

            'Set right to the bottom of the sheet
            WkSht.Range(WkSht.Cells(2, LngCol), WkSht.Cells(WkSht.Rows.Count, LngCol)).NumberFormat = "dd/mmm/yyyy"

        End If

    Next

Next

End Sub

Hopefully that has all made sense, this does work on the premise that the header row is always row 1 and there are no gaps in the columns, but these are separate issues you can approach when you're ready to.

How to select column and display its current format using VBA Macro?

1 Answers