Running a Macro on single or range of columns

Olendris63 0
Viewed 33

I believe this should be an easy one for you vets out there...

I have the following Macro that will change a "data format of any kind" to m/d/yyyy format, but with the "ActiveCell.Column" portion, I have to select each column 1 at a time. Wondering if there's a way to have it work on individual columns or a range of columns selected... My first thought was to simply change "ActiveCell.Column" to "ActiveCell.Columns", but this didn't work... VBA code below:

Sub change_2_short_dt()

    a = ActiveCell.Column
    Columns(a).Select
    Selection.NumberFormat = "m/d/yyyy"
End Sub
formatting vba excel
2 Answers

Normally one would avoid using Select, e.g.

Columns(1).NumberFormat = "m/d/yyyy" ' single column
Columns("A").NumberFormat = "m/d/yyy" ' can also use column letter
Columns("A:E").NumberFormat = "m/d/yyyy" ' multiple columns

If you want to rely on the Selection:

Selection.EntireColumn.NumberFormat = "m/d/yyy"
BigBen0

Reference (Select) Columns

Option Explicit

Sub change_2_short_dt()

    Dim rg As Range
    
    ' Validate the selection.
    If Not TypeOf Selection Is Range Then
        MsgBox "The selection is not a range.", vbExclamation
        Exit Sub
    Else
        ' Reference the range ('rg'). 
        With Selection

            ' Entire worksheet columns.
            Set rg = .EntireColumn

            ' Worksheet used range columns (no columns outside it).
            'Set rg = Intersect(.EntireColumn, .Worksheet.UsedRange)

            ' Columns starting in the same row and of same size
            ' as the columns of the worksheet used range.
            'Set rg = Intersect(.EntireColumn, .Worksheet.UsedRange.EntireRow)

        End With
    
    End If
    
    ' Test with 'Select' to see what is selected (referrenced).
    rg.Select
    MsgBox "Selection Address " & rg.Address(0, 0), vbInformation
    ' When done testing, use the following instead:

    ' Apply number format.        
    'rg.NumberFormat = "m/d/yyyy"
    'MsgBox "Selection Address: " & rg.Address(0, 0) & vbLf _
        & "Selection Number Format: " & rg.NumberFormat, vbInformation

End Sub
VBasic20080
Related