CODEWITHSUNDEEP
vbaexcel
Big_Papa_B
Big_Papa_B

Reputation: 139

Auto Populate Formula to end of series in excel

I am having an issue with a piece of VBA in excel and am looking for assistance.

I require a piece of code to auto populate a formula in excel through a series of data, the series will vary in length and will occupy columns C:I.

I have been using this piece of code without issue for quiet a while:

Sub Auto_Fill_Formula()
Sheets("Sheet1").Select

Dim LstRow As Long

With Sheets("Sheet1")
     LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
     .Range("A2:A" & LastRow).Formula = "Formula added here"
      Application.CutCopyMode = False

End With

End Sub

However as the formula is being added to the leftmost column it only populates the first cell, cell A2.

How can I modify this code to work when the leftmost column is empty?

Thank you,

Wayne

Upvotes: 1

Views: 49

Answers (1)

SJR
SJR

Reputation: 23081

You can use Find to find the last used row in C:I by searching backwards from row 1. You should also use Option Explicit to pick up typos in variable names (LstRow).

Sub Auto_Fill_Formula()

Sheets("Sheet1").Select

Dim LastRow As Long, r As Range

With Sheets("Sheet1")
     Set r = .Range("C:I").Find(What:="*", After:=.Range("C1"), Lookat:=xlWhole, SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
     If Not r Is Nothing Then
        LastRow = r.Row
        .Range("A2:A" & LastRow).Formula = "Formula added here"
      End If
End With

End Sub

Upvotes: 2

Related Questions