<div>
Hi,<br />
<br />
Try below:<br />

<pre><code id="code-20-42150332-1">Sub ChangeFormat()
Dim Ws As Worksheet
Dim LC As Integer
Dim i As Long
Application.ScreenUpdating = False
Set Ws = ActiveSheet
LC = Ws.Cells(1, Ws.Columns.Count).End(xlToLeft).Column
For i = 1 To LC
    Ws.Cells(1, i).NumberFormat = &quot;mmm-yy&quot;
Next i
Application.ScreenUpdating = True
End Sub</code></pre>
</div>


Hi,

Try below:


Sub ChangeFormat()
Dim Ws As Worksheet
Dim LC As Integer
Dim i As Long
Application.ScreenUpdating = False
Set Ws = ActiveSheet
LC = Ws.Cells(1, Ws.Columns.Count).End(xlToLeft).Column
For i = 1 To LC
    Ws.Cells(1, i).NumberFormat = "mmm-yy"
Next i
Application.ScreenUpdating = True
End Sub

<div>
Thanks Shums! And if I wanted to start in column H then can I change the For loop to start at 8?<br />
<br />

<pre><code id="code-20-42150345-1">For i = 8 To LC
    If IsDate(Ws.Cells(1, i)) Then
        Ws.Cells(1, i).NumberFormat = &quot;mmm-yy&quot;
    End If
Next i</code></pre>
</div>


Thanks Shums! And if I wanted to start in column H then can I change the For loop to start at 8?



For i = 8 To LC
    If IsDate(Ws.Cells(1, i)) Then
        Ws.Cells(1, i).NumberFormat = "mmm-yy"
    End If
Next i

<div>
<div class="content wysiwyg-content">
Hi all.<br />
<br />
I'd like to custom format to mmm-yy the column headers starting with H1 and going on to I1, J1, K1, L1 etc. as long as the cells in each column header have data.<br />
<br />
I don't know how many columns will be in the spreadsheet beforehand as it is all based on the end users parameters. Sometimes there may be just 2 column headers H1 and I1, other times there might be 30 etc.<br />
<br />
Thank you in advance!
</div>
</div>


Hi all.

I'd like to custom format to mmm-yy the column headers starting with H1 and going on to I1, J1, K1, L1 etc. as long as the cells in each column header have data.

I don't know how many columns will be in the spreadsheet beforehand as it is all based on the end users parameters. Sometimes there may be just 2 column headers H1 and I1, other times there might be 30 etc.

Thank you in advance!

Custom Format uknown number of column headers to mmm-yy Excel 2016 VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.