0

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
Community
  • 1
  • 1
min
  • 5
  • 2
  • 2
    https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – cybernetic.nomad Jun 15 '22 at 14:28
  • 1
    ... i.e. e.g. `Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).EntireColumn.Insert` if all cells to the right of the first empty cell are expected to be empty, or `Range("AL2", Cells(2, Columns.Count)).Find("", Cells(2, Columns.Count), xlFormulas, xlWhole).EntireColumn.Insert` if not. – VBasic2008 Jun 15 '22 at 14:42

1 Answers1

2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • wow, thank you! Just by reading your code I'm learning so much. I have one general question, if you don't mind answering. Is it generally recommended to define worksheets? – min Jun 15 '22 at 15:00
  • @min yes, every `Range`, `Cells`, `Columns` and `Rows` object should have a worksheet specified. Otherwise it depends on where you write the code it will assume a default sheet (many times `ActiveSheet` but not always). Therefore it is highly recommended to define the worksheets. If you just write `Range("AL2")` it is not clear in which sheet that range is supposed to be! – Pᴇʜ Jun 15 '22 at 15:11