How to determine the last row in a given worksheet

[C C]

Hello.

I have a macro that formats the worksheet from a text file to an excel file.
I insert a column and create a formula on the second row of that column.
How do I determine the last row in the worksheet so I will paste my
formula until the last row on the new column?

Thanks in advance.

 

[Don Guillett]

try
lastrow=cells(rows.count,"a").end(xlup).row

 

[C C]

Thanks. So after getting the "lastrow"
how do you, say, select the range from A2 to
the lastrow on column A?

Thanks again.

 

[Don Guillett]

Post YOUR macro coding efforts for comments.

 

[Dave Peterson]

Dim LastRow as long

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup)
.range("a2:a" & lastrow).select
end with

Is one way.

Although there is very little that requires that you select a range before you
work with it.

 

[Norman Jones]

Hi C C,

Try:
'===========>>
Sub aTester()
Dim LRow As Long
Const col As String = "B" '<<==== CHANGE

LRow = Cells(Rows.Count, "A").End(xlUp).Row
With Range(col & 2)
.AutoFill Destination:=.Resize(LRow - 1)
End With

End Sub
'<<===========

Change B to reflect the new column.

 

[Bob Phillips]

Small omission by Dave

Dim LastRow as long

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup)

should be

lastrow = .cells(.rows.count,"A").end(xlup).Row

 

[Dave Peterson]

Oopsie.

Thanks for the correction, Bob.

Small omission by Dave



should be

lastrow = .cells(.rows.count,"A").end(xlup).Row

 

[C C]

Thank you all so much. I got my macro running the way I want
it.