Excel VBA Dynamic Range / Loop issue

Question

I am developing a financial model for a bank and come across the below issue which I am not able to resolve in Excel VBA, and would appreciate your help.

I have built a simple macro which essentially does two things: (i) it clears contents in a given range, (ii) it populates the same range with a formula. In a very abbreviated way it looks like the following:

Sub AutoCalculateCashFlows()
    Range(D208:L208).ClearContents
    Range("L208").FormulaR1C1 = "=+R[-34]C-R[-34]C[-1]"
    Range("L208").AutoFill Destination:=Range("E208:L208"), Type:=xlFillDefault
End Sub

My problem is that the range that should be auto populated is dependent on how many cells did the user fill in within the range of E10:L10. Users will start populating this range from right to left, but I don't know how far they will go from column L to the left. The formula that my macro auto populates needs at least two data, ie. at least L10 and K10 should be populated and if the latter is the case then the macro only needs to auto populate L208 with formula, in case J10:L10 is filled out then the macro needs to auto populate the range L208:K208 and so on to the point that in case the full D10:L10 range is filled out then E208:L208 should be populated with formula.

I have thought to resolve this issue via two routes: (i) approaching it as a dynamic range problem in which case I need a vba code to determine the previous to the last cell populated by the user in the range D10:L10 and use the column code of that cell in "Destination:=Range("E208:L208")", or (ii) run a loop which will populate range E208:L208 with formula until the cell in the previous column within range D10:L10 is filled in by the user and stop when it is not.

Hope this makes sense and thanks in advance for the help.

Vityata Vityata · Accepted Answer · 2017-11-23T10:55:03

When you need a dynamic range in VBA, you should simply build one. This is probably the easiest method:

Sub TestMe()

    Dim colRange    As Long
    Dim rowRange    As Long
    Dim rngMain     As Range

    rowRange = 10

    With Worksheets(1)
        colRange = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set rngMain = .Range(.Cells(rowRange, colRange), .Cells(100, 200))
        MsgBox rngMain.Address
    End With

End Sub

It is dynamic, based on the last used column in row 1 of the first Worksheet.

Concerning the second used column in Row 1, one of these 3 would probably do it for you, depending on what exactly do you want:

.Cells(1, 1).End(xlToRight).End(xlToRight).Column
.Cells(1, 1).End(xlToRight).Column
.Cells(1, 1).End(xlToRight).Column + 1

Excel VBA Dynamic Range / Loop issue

1 Answers