Formating by column header instead of alphabet

Question

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

You can use Names in Excel to define unique names for cells, ranges, rows, columns. You can also use "real tables" and refer to table headers instead of column ranges — Gowire, Jan 03 '23 at 09:03
@pravinbindage, you'd better give more info as to how _"column position is changing every time"_ — user3598756, Jan 03 '23 at 09:16
@user3598756 It is quite common that Excel sheets are dynamic and that column positions are moved due to added/removed columns to the left. I rarely use static references (e.g. "A1") to ranges when using VBA. That's what I guess is meant by "column positions is changing". If you delete column G, then Column J will become Column I. That's the problem in this case... I guess — Gowire, Jan 03 '23 at 09:26
@Gowire, rest sure that, after 25+years of Excel VBA coding, I know that. I was asking for more detail about the criteria of the columns change. Let's see what pravin will answer — user3598756, Jan 03 '23 at 09:45

Gowire · Answer 1 · 2023-01-03T09:39:32.407

1

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

edited Jan 03 '23 at 09:39

answered Jan 03 '23 at 09:17

Gowire

1,046
6
27

it throws error _ Type Mismatch on this line -> Set Rng = ActiveSheet.Columns("MyColumn") – pravin bindage Jan 03 '23 at 09:36
@pravinbindage Ah, sorry... A quick copy/paste error. It should be `Range` instead of `Column`. The code is corrected now – Gowire Jan 03 '23 at 09:40
now it throw another error - application defined 0r object defined error – pravin bindage Jan 03 '23 at 09:47
@pravinbindage Have you defined the name for your column? It must be the same as in the VBA code. – Gowire Jan 03 '23 at 09:51
Yes i defined that – pravin bindage Jan 03 '23 at 09:54

score 1 · Answer 2 · answered Jan 03 '23 at 10:02

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.

Formating by column header instead of alphabet

2 Answers2