Improve Macro Efficiency (VBA)-CodePudding

This code works but I feel like there could be a better way to write it as I'm new to VBA and don't really know much about it. Any suggestion is appreciated!

Basically, starting from cell AL2, I'm going through each column until I find the first empty cell in row 2. Then, I'm inserting a new column left to that empty cell. Finding the empty cell is almost instant but it takes around 15-20 seconds for it to insert a new column. Here is my code:

Range("AL2").Select
Do Until IsEmpty(ActiveCell)
    ActiveCell.Offset(0, 1).Select
Loop

ActiveCell.EntireColumn.Insert

CodePudding user response：

Do not use .Select and instead of looping and testing every cell you can just use StartCell.End(xlToRight) to jump right to last used cell.

Option Explicit

Public Sub InsertColumn_Example()
    Dim ws As Worksheet
    Set ws = ActiveSheet  ' better define your worksheet like `Set ws =ThisWorkbook.Worksheets("Sheet1")
    
    ' find next empty cell in the row of StartCell
    Dim NextEmptyCell As Range
    Set NextEmptyCell = GetNextEmptyCellByColumn(StartCell:=ws.Range("AL2"))
    
    ' insert column
    NextEmptyCell.EntireColumn.Insert
End Sub

Public Function GetNextEmptyCellByColumn(ByVal StartCell As Range) As Range
    If IsEmpty(StartCell) Then
        ' if StartCell cell is empty return it
        Set GetNextEmptyCellByColumn = StartCell
    ElseIf IsEmpty(StartCell.Offset(ColumnOffset:=1)) Then
        ' if cell right of StartCell is empty return it
        Set GetNextEmptyCellByColumn = StartCell.Offset(ColumnOffset:=1)
    Else
        ' otherwise jump to the next empty cell right of StartCell 
        Set GetNextEmptyCellByColumn = StartCell.End(xlToRight).Offset(ColumnOffset:=1)
    End If
End Function