How to determine the last row in a given worksheet

C

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.
 
C

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.
 
D

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.
 
N

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.
 
B

Bob Phillips

Small omission by Dave

Dave Peterson said:
Dim LastRow as long

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

should be

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