How to determine the last row in a given worksheet

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.

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


--
Don Guillett
SalesAid Software
[email protected]
"C C" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> try
> lastrow=cells(rows.count,"a").end(xlup).row
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "C C" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>>
>>
>
>

Post YOUR macro coding efforts for comments.

--
Don Guillett
SalesAid Software
[email protected]
"C C" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > try
> > lastrow=cells(rows.count,"a").end(xlup).row
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "C C" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >>
> >
> >
>
>

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.



C C wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > try
> > lastrow=cells(rows.count,"a").end(xlup).row
> >
> >
> > --
> > Don Guillett
> > SalesAid Software
> > [email protected]
> > "C C" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >>
> >>
> >
> >

--

Dave Peterson

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.


---
Regards,
Norman



"C C" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> try
>> lastrow=cells(rows.count,"a").end(xlup).row
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> "C C" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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.
>>>
>>>
>>
>>
>
>

Small omission by Dave

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Dim LastRow as long
>
> with activesheet
> lastrow = .cells(.rows.count,"A").end(xlup)

should be

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

> .range("a2:a" & lastrow).select
> end with

Oopsie.

Thanks for the correction, Bob.

Bob Phillips wrote:
>
> Small omission by Dave
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > Dim LastRow as long
> >
> > with activesheet
> > lastrow = .cells(.rows.count,"A").end(xlup)
>
> should be
>
> lastrow = .cells(.rows.count,"A").end(xlup).Row
>
> > .range("a2:a" & lastrow).select
> > end with

--

Dave Peterson

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> Small omission by Dave
>
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
>> Dim LastRow as long
>>
>> with activesheet
>> lastrow = .cells(.rows.count,"A").end(xlup)
>
> should be
>
> lastrow = .cells(.rows.count,"A").end(xlup).Row
>
>> .range("a2:a" & lastrow).select
>> end with
>
>