VBA code to insert an empty column from coolumn I to column HL when the numbering changes in cell $I$4:$H$L4

Question

Dear Sir / Madam,

Is there any VBA codes to run in order to execute this task?

Thanks in advance.

Low

Answer 1

Hi Mr.Low,

I've understood your requirements is to insert a blank column at the position where the numbering changes.

Here's a VBA code snippet that traverses from column HL to column I in reverse, inserting a blank column to the right of the current one whenever it detects a change in value. This approach may meet your needs.

Sub InsertColumnOnNumberChangeRefined() 

    Dim ws As Worksheet 

    Dim startCol As Long, endCol As Long 

    Dim currentValue As Variant 

    Dim prevValue As Variant 

    Set ws = ActiveSheet 

    startCol = 9 

    endCol = 220 

    For currentCol = endCol To startCol Step -1 

        currentValue = ws.Cells(4, currentCol).Value 

        If currentCol = endCol Then 

            prevValue = currentValue 

        Else 

            If currentValue <> prevValue Then 

                ws.Columns(currentCol + 1).Insert Shift:=xlToLeft 

                currentCol = currentCol + 1 

            End If 

            prevValue = currentValue 

        End If 

    Next currentCol 

End Sub

Please feel free to let me know how it goes.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Jonathan,

Yes, the VBA code works perfectly well.

Many thanks for your quick response.

Best Regards

Low