CODEWITHSUNDEEP
vbaexcel
SMORF
SMORF

Reputation: 499

Use the last row count in a formula

The following code allows me to repeat a formula from row 2 to all rows up to the last active row.

Dim LastRow As Long

With Sheets("C PUR TYPE")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("M2:M" & LastRow).formula = "=IFERROR(RIGHT(SUBSTITUTE(Lookup_concat(A2,$A$2:$A1932,$L$2:$L1932),"" 0,"",""""),LEN(SUBSTITUTE(Lookup_concat(A2,$A$2:$A1932,$L$2:$L1932),"" 0,"",""""))-2),"" - "")"
End With

In this instance there are 1'932 active rows.

Is there a way that I can replace the cell reference :$A1932 and $L1932 in the formula to using the last row count as the row count will be different each time the report runs.

Thanks

Upvotes: 2

Views: 907

Answers (2)

theholyhandgrenade
theholyhandgrenade

Reputation: 82

Seems to me like you can use a variable similar to how you calculated lastRow.

Example (assuming you don't have any blanks in column A):

dim LookupLastrow as long

LookupLastrow=range("A1").end(xldown).row

then replace the 1932 references in your formula with " & lookuplastrow & ":

.Range("M2:M" & LastRow).formula = "=IFERROR(RIGHT(SUBSTITUTE(Lookup_concat(A2,$A$2:$A" & lookuplastrow & ",$L$2:$L" & lookuplastrow & "),"" 0,"",""""),LEN(SUBSTITUTE(Lookup_concat(A2,$A$2:$A" & lookuplastrow & ",$L$2:$L" & lookuplastrow & "),"" 0,"",""""))-2),"" - "")"

Upvotes: -1

Chrismas007
Chrismas007

Reputation: 6105

Using & will allow you to concatenate the variable into the string:

Dim LastRow As Long

With Sheets("C PUR TYPE")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("M2:M" & LastRow).formula = "=IFERROR(RIGHT(SUBSTITUTE(Lookup_concat(A2,$A$2:$A" & LastRow & ",$L$2:$L" & LastRow & "),"" 0,"",""""),LEN(SUBSTITUTE(Lookup_concat(A2,$A$2:$A" & LastRow & ",$L$2:$L" & LastRow & "),"" 0,"",""""))-2),"" - "")"
End With

Upvotes: 3

Related Questions