CODEWITHSUNDEEP
vbaexcelexcel-formulavlookup
Sherbetdab
Sherbetdab

Reputation: 127

Range linked to last cell in adjoining column

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

Upvotes: 1

Views: 117

Answers (2)

user4039065
user4039065

Reputation:

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.

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.

Upvotes: 1

TheGuyThatDoesn'tKnowMuch
TheGuyThatDoesn'tKnowMuch

Reputation: 1250

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.

Upvotes: 2

Related Questions