Formating by column header instead of alphabet-CodePudding

I want to do some column formatting but column position is changing every time so how can i use column header instead of Column Alphabet ?

Sub Excel_Format()
    Columns("J:J").Select
    Selection.NumberFormat = "0"
    Selection.Columns.AutoFit
End Sub

CodePudding user response：

You need to start defining a Name for your column. The quickest way to do it is to select the column (Column J in this case) and enter a name in the range/cell selector on the top-left part of your Workbook. See image below:

I have named the column to "MyColumn". You can now use this as a reference in your code, like this:

Sub Excel_Format()
    Dim Rng As Range

    Set Rng = ActiveSheet.Range("MyColumn")
    Rng.NumberFormat = "0"
    Rng.Columns.AutoFit
End Sub

Even if you add or remove columns to the left of column J, the reference to MyColumn will remain correct

CodePudding user response：

Please, try the next way. You can use it for any header, only adapting the used constant:

Sub Excel_Format()
    Dim ws As Worksheet, RngH As Range
    Const myColName As String = "ColumnX" 'your column header
    
    Set ws = ActiveSheet 'use here your necessary sheet
    
    Set RngH = ws.rows(1).Find(myColName)
    If Not RngH Is Nothing Then
        With RngH.EntireColumn
            .NumberFormat = "0"
            .AutoFit
        End With
    Else
        MsgBox myColName & " could not be found in the sheet first row..."
    End If
End Sub

The header should exist in the first sheet row. If not, you should adapt ws.rows(1).Find( writing the necessary row, instead of `...

Selecting, activating in such a context only consumes Excel resources, not bringing any benefit.