Range linked to last cell in adjoining column

Question

I have a worksheet with 4 columns, I've written a macro to put formula in columns A + B that are dependent on what is in column D, then copy and paste as values to enable filtering of those columns. My problem is that on a weekly basis column D becomes longer. I don't want to have to keep changing the values in my Macro for the range of A + B (A2:A69422) where 69422 is the last used cell in column D.

Worksheets("salesinfo").Range("B2").Formula = "= MID(D3,3,5)"
    Range("B2").Select
        Selection.AutoFill Destination:=Range("B2:B69422")
        Range("B2:B69422").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

    Selection.Value = Selection.Value

    Range("A2").Formula = "= VLOOKUP(B2,[Data.xlsb]Stores!$A:$X,4,0)"

    Range("A2").Select
        Selection.AutoFill Destination:=Range("A2:A69422")
        Range("A2:A69422").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

            Workbooks("Data.xlsb").Close SaveChanges:=False

            Application.CutCopyMode = False

            Range("A2").Select

score 2 · Answer 1 · answered Jun 13 '16 at 19:23

2

Use the following syntax to determine the last row in Column D, then assign it to a Long or Integer variable. In my example I'm using lRow as my variable:

Dim lRow As Long

With ActiveSheet

    lRow = .Cells(.Rows.Count, 4).End(xlUp).Row

End With

Then just use lRow + 1 in your row reference in the cell address.

answered Jun 13 '16 at 19:23

TheGuyThatDoesn'tKnowMuch

1,240
1
17
28

Thank You so much, for "TheGuyThatDoesn'tKnowMuch" you know a helluva lot. One small probelm, column B ends up one cell short and column A ends up 1 cell too long. Thanks Again. – Sherbetdab Jun 13 '16 at 19:46

score 1 · Answer 2 · edited May 23 '17 at 10:29

As noted by TheGuyThatDoesn'tKnowMuch, declaring a Long type variable and storing the last populated row of the mode relevant column is best accomplished by looking from the bottom up. This is the equivalent of starting at the bottom of the worksheet and tapping Ctrl+▲.

Here are a few other ways you can improve your code.

Define your Workbook Object and Worksheet Object with a With ... End With statement and use that reference by prefixing each Range object with a . like .Range(...). Don't leave .Parent worksheet references implicit; always make them explicit.
Write all of your formulas at once; no need to use the Range.AutoFill method.
Direct value transfer is more efficient than a Copy & Paste Special, Values and does not involve the clipboard.
Avoid the use of the Range.Select and Range.Activate methods¹. They may be a good way to get started with VBA but they are also the first thing you should be graduating away from.

Referencing columns A:X in the external workbook within the VLOOKUP function when you only require A:D is inefficient; in any event, a INDEX/MATCH function pair may be better all around for a large number of rows.

You are writing a formula into column A that references the external workbook Data.xlsb so I know that is not the worksheet containing the salesinfo worksheet. You should adjust the workbook reference below to avoid the use of the ActiveWorkbook property.

    Dim lr As Long

    With ActiveWorkbook
        With .Worksheets("salesinfo")
            lr = .Cells(.Rows.Count, "D").End(xlUp).Row
            With .Range("B2:B" & lr)
                .Formula = "=MID(D3, 3, 5)"
                .Value = .Value
            End With
            With .Range("A2:A" & lr)
                .Formula = "=VLOOKUP(B2, [Data.xlsb]Stores!$A:$X, 4, FALSE)"
                'alternate formula
                '.Formula = "=INDEX([Data.xlsb]Stores!$D:$D, MATCH(B2, [Data.xlsb]Stores!$A:$A, 0))"
                .Value = .Value
            End With
            .Range("A2").Activate
        End With
    End With

    Workbooks("Data.xlsb").Close SaveChanges:=False

The formula destined for column B concerns me a little. You are referencing D3 from the formula in B2. This means that if the formula is populated down column B to match all of the values in column D then the last formula will actually be referencing a blank cell below the last populated cell in column D.

If you run into calculation lag problems (taking too long) writing two columns of formulas into nearly 70K rows, a dictionary object and variant arrays could speed things up very appreciably.

.¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Thanks Jeeped, Works a treat, only takes a matter of seconds but saves me quite a bit of time, i'm only just starting out using vba and your help is greatly appreciated. I have a couple of books, vba for dummies for one, can you point me in the direction of any good self learning sites? Although by just viewing posts on here i managed to write this with a little bit of help, well a big bit actually. Thank You. PS.D3 should read D2, thanks for pointing that out. — Sherbetdab, Jun 13 '16 at 21:13
You will likely learn VBA in spurts; pick a task and research each 'section' or operation that makes up that task. Put something together and if you run into trouble, post a new question back here showing what you have come up with and note any problems or errors. VBA is very much a DIY type language; I believe it is intentionally structured that way. Keep a collection of 'snippets' that demonstrate various aspects that you can refer back to. — , Jun 13 '16 at 23:45

Range linked to last cell in adjoining column

2 Answers2