[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Macro to go to last cell in a column

excelnut1954

12/15/2006 2:39:00 PM

Currently, I have this simple macro to find the next empty row to paste
input from a user.

Range("B65536").End(xlUp).Offset(1, -1).Select
ActiveSheet.Paste


Works fine. But, I'd like to have it go to a range name, which would be
the column header, then go to row 65536, then up. Something like this:
Application.Goto Reference:="Column Header"
Range("65536").End(xlUp).Offset(1, -1).Select '*** took B out of the
range reference***
ActiveSheet.Paste

I know it won't work like this, since I took the "B" out of the Range.
But, I want to eliminate the column letter reference, in case of a
redesign later, where I move columns.
Can I have it as simple as the original macro above, but without the
column letter?
Thanks,
J.O.
3 Answers

Nik

12/15/2006 2:56:00 PM

0

excelnut1954 wrote:
> Currently, I have this simple macro to find the next empty row to paste
> input from a user.
>
> Range("B65536").End(xlUp).Offset(1, -1).Select
> ActiveSheet.Paste
>
>
> Works fine. But, I'd like to have it go to a range name, which would be
> the column header, then go to row 65536, then up. Something like this:
> Application.Goto Reference:="Column Header"
> Range("65536").End(xlUp).Offset(1, -1).Select '*** took B out of the
> range reference***
> ActiveSheet.Paste
>
> I know it won't work like this, since I took the "B" out of the Range.
> But, I want to eliminate the column letter reference, in case of a
> redesign later, where I move columns.
> Can I have it as simple as the original macro above, but without the
> column letter?
> Thanks,
> J.O.
>
Is there data in every cell to the left of the one you want to paste in?
I don't use named ranges, but how about finding the intersect of
range("a65536").end(xlup).entirerow
and
named_range.entirecolumn?

Nik

Don Guillett

12/15/2006 3:40:00 PM

0

Sub pastetolast()
lr = Cells(Rows.Count, "b").End(xlUp).Row + 1
Range("i1").Copy Range("b" & lr)
End Sub

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"excelnut1954" <excelnut1954@yahoo.com> wrote in message
news:1166193519.029708.173260@l12g2000cwl.googlegroups.com...
> Currently, I have this simple macro to find the next empty row to paste
> input from a user.
>
> Range("B65536").End(xlUp).Offset(1, -1).Select
> ActiveSheet.Paste
>
>
> Works fine. But, I'd like to have it go to a range name, which would be
> the column header, then go to row 65536, then up. Something like this:
> Application.Goto Reference:="Column Header"
> Range("65536").End(xlUp).Offset(1, -1).Select '*** took B out of the
> range reference***
> ActiveSheet.Paste
>
> I know it won't work like this, since I took the "B" out of the Range.
> But, I want to eliminate the column letter reference, in case of a
> redesign later, where I move columns.
> Can I have it as simple as the original macro above, but without the
> column letter?
> Thanks,
> J.O.
>


excelnut1954

12/19/2006 1:49:00 PM

0

Thanks for the replies. I'll work on these, and see if either one will
work for what I'm doing.
Thanks again!!
J.O.



Don Guillett wrote:
> Sub pastetolast()
> lr = Cells(Rows.Count, "b").End(xlUp).Row + 1
> Range("i1").Copy Range("b" & lr)
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> dguillett1@austin.rr.com
> "excelnut1954" <excelnut1954@yahoo.com> wrote in message
> news:1166193519.029708.173260@l12g2000cwl.googlegroups.com...
> > Currently, I have this simple macro to find the next empty row to paste
> > input from a user.
> >
> > Range("B65536").End(xlUp).Offset(1, -1).Select
> > ActiveSheet.Paste
> >
> >
> > Works fine. But, I'd like to have it go to a range name, which would be
> > the column header, then go to row 65536, then up. Something like this:
> > Application.Goto Reference:="Column Header"
> > Range("65536").End(xlUp).Offset(1, -1).Select '*** took B out of the
> > range reference***
> > ActiveSheet.Paste
> >
> > I know it won't work like this, since I took the "B" out of the Range.
> > But, I want to eliminate the column letter reference, in case of a
> > redesign later, where I move columns.
> > Can I have it as simple as the original macro above, but without the
> > column letter?
> > Thanks,
> > J.O.
> >