CODEWITHSUNDEEP
excelvba
ERKSMTY
ERKSMTY

Reputation: 135

Formula referencing last row VBA

I'm passing a bunch of concatenated data into an ODBC workbook via a single Excel cell. The cell changes based on the number of rows in the data. To manage this, I want to insert a formula into a second cell (using VBA) that looks something like this...

With Sheets("MAIN")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With    

Sheets("MAIN").Range("A1").Formula = "=("B" & LastRow)"

This code doesn't work. I think it's close, but I'm a hack. Can any of you point me in the right direction?

Upvotes: 0

Views: 182

Answers (1)

YowE3K
YowE3K

Reputation: 23974

I think you want:

Sheets("MAIN").Range("A1").Formula = "=B" & LastRow

And, if this is the entire code in this section of your application, it would be good (although definitely not required) to include the last line within the With block, i.e.

With Sheets("MAIN")
    LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("A1").Formula = "=B" & LastRow
End With

Upvotes: 1

Related Questions