CODEWITHSUNDEEP
excelvbaperformance
min
min

Reputation: 5

Improve efficiency of inserting a column

This code works but I feel there could be a better way.

Starting from cell AL2, I go through each column until I find the first empty cell in row 2.
Then, I insert a new column left to that empty cell.

Finding the empty cell is almost instant but it takes around 15-20 seconds to insert a new column.

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

ActiveCell.EntireColumn.Insert

Upvotes: 0

Views: 87

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 2

Related Questions