Select 2nd Row through last Row in Last Column +1 VBA

Question

so I know how to get VBA to figure out what the last row is, what the last column is, adding +1 to go to the next empty row or column. What I am trying to do is insert a formula in the column next to the last column, i.e. the empty column, but only in rows 2 through the last row. Can't seem to get it with a Range() since the lastColumn variable is a number; can't use Cells() since it won't take the range "2:" lastRow. Any suggestions?

EDIT: The link that was suggested as a duplicate does not address my question. The user didn't read, or comprehend, my question.

Asked many times before. Use the `xlup` `xltoleft` approaches, or better again `Find` to get the last used column/row etc — brettdj, Jul 30 '14 at 02:41
Not sure why this was marked as a duplicate. The link provided does not answer my question. Thanks gtwebb for actually reading my question and providing and answer. — cjones, Sep 05 '14 at 19:12

score 2 · Accepted Answer · answered Jul 29 '14 at 23:05

2

You can also use cells within the range call but you need the

Range(Cells(2, lastcolumn+1), Cells(lastrow, lastcolumn+1)).Formula = "=sum(1,1)"

Which would be the same as range("B2:B" & lastrow) if A is the last column

answered Jul 29 '14 at 23:05

gtwebb

2,981
3
13
22

Siphor · Answer 2 · 2014-07-29T23:02:50.183

1

You can use Cells for the first cell and then resize the range

Cells(2,lastColumn+1).Resize(lastRow-2+1,1)

A more hackish solution would be

Range(Letter(lastColumn+1) & 2 & ":" & Letter(lastColumn+1) & lastRow)

Function Letter(dim columnNumber as long)
    Letter=Split(Worksheets(1).Cells(1,columnNumber).Address, "$")(1)
End function

edited Jul 29 '14 at 23:02

answered Jul 29 '14 at 21:48

Siphor

2,522
2
13
10

Select 2nd Row through last Row in Last Column +1 VBA

2 Answers2