Hi Guys,

I need some assistance with a macro. I need the macro to grab some formulas
from a static range of cells (B2 - G2) and paste that formula in a static
range (B4 - G4) and then I need it to autofill to the last empty row (the end
of this range is always changing or else I would use a static end range).
For example, today I might need it to fill all the way to B36 - G36 and
tomorrow I might need it to fill to B77 - G77. The end of the range is
determined by the column immediately preceeding the formulas. In my example
above A36 or A77.

A B C D E F G H
1 x formulas............
2 x fill formulas........
3 x fill formulas........
4
5
6 x end formula fill....
7
8
9
The copy and paste section of the macro is working fine, it's the autofil to
an always changing end row that I'm having trouble with. Hope this makes
sense.

This is what I have been using...maybe I'm not even close...

lRow = Range("AG4").End(xlDown).Row
Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)

Thanks as always for your help!!
Kelley

spaces??
maybe
'>>lRow = Range("AG4").End(xlDown).Row
lRow = cells(rows.count,"AG4").End(xlUP).Row

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Kell2604" <(E-Mail Removed)> wrote in message
news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
> Hi Guys,
>
> I need some assistance with a macro. I need the macro to grab some
> formulas
> from a static range of cells (B2 - G2) and paste that formula in a static
> range (B4 - G4) and then I need it to autofill to the last empty row (the
> end
> of this range is always changing or else I would use a static end range).
> For example, today I might need it to fill all the way to B36 - G36 and
> tomorrow I might need it to fill to B77 - G77. The end of the range is
> determined by the column immediately preceeding the formulas. In my
> example
> above A36 or A77.
>
> A B C D E F G H
> 1 x formulas............
> 2 x fill formulas........
> 3 x fill formulas........
> 4
> 5
> 6 x end formula fill....
> 7
> 8
> 9
> The copy and paste section of the macro is working fine, it's the autofil
> to
> an always changing end row that I'm having trouble with. Hope this makes
> sense.
>
> This is what I have been using...maybe I'm not even close...
>
> lRow = Range("AG4").End(xlDown).Row
> Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
>
> Thanks as always for your help!!
> Kelley

Hello Don,

Thanks for your help. Just to confirm are you suggesting I swap out my
original line lRow = Range("AG4").End(xlDown).Row with what you have below...
lRow = cells(rows.count,"AG4").End(xlUP).Row. I tried that and am getting
the error: application-defined or object-defined error.

Or am I misunderstanding you?

"Don Guillett" wrote:

>
> spaces??
> maybe
> '>>lRow = Range("AG4").End(xlDown).Row
> lRow = cells(rows.count,"AG4").End(xlUP).Row
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Kell2604" <(E-Mail Removed)> wrote in message
> news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
> > Hi Guys,
> >
> > I need some assistance with a macro. I need the macro to grab some
> > formulas
> > from a static range of cells (B2 - G2) and paste that formula in a static
> > range (B4 - G4) and then I need it to autofill to the last empty row (the
> > end
> > of this range is always changing or else I would use a static end range).
> > For example, today I might need it to fill all the way to B36 - G36 and
> > tomorrow I might need it to fill to B77 - G77. The end of the range is
> > determined by the column immediately preceeding the formulas. In my
> > example
> > above A36 or A77.
> >
> > A B C D E F G H
> > 1 x formulas............
> > 2 x fill formulas........
> > 3 x fill formulas........
> > 4
> > 5
> > 6 x end formula fill....
> > 7
> > 8
> > 9
> > The copy and paste section of the macro is working fine, it's the autofil
> > to
> > an always changing end row that I'm having trouble with. Hope this makes
> > sense.
> >
> > This is what I have been using...maybe I'm not even close...
> >
> > lRow = Range("AG4").End(xlDown).Row
> > Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
> >
> > Thanks as always for your help!!
> > Kelley
>
>

Don, what is the difference between finding the last row as you are doing
here, and doing it like this?

LstRow = [A65000].End(xlUp).Row

Is one method better than the other in certain situations? And if so, why?
Just trying to understand. Thanks

Greg

"Don Guillett" wrote:

>
> spaces??
> maybe
> '>>lRow = Range("AG4").End(xlDown).Row
> lRow = cells(rows.count,"AG4").End(xlUP).Row
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Kell2604" <(E-Mail Removed)> wrote in message
> news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
> > Hi Guys,
> >
> > I need some assistance with a macro. I need the macro to grab some
> > formulas
> > from a static range of cells (B2 - G2) and paste that formula in a static
> > range (B4 - G4) and then I need it to autofill to the last empty row (the
> > end
> > of this range is always changing or else I would use a static end range).
> > For example, today I might need it to fill all the way to B36 - G36 and
> > tomorrow I might need it to fill to B77 - G77. The end of the range is
> > determined by the column immediately preceeding the formulas. In my
> > example
> > above A36 or A77.
> >
> > A B C D E F G H
> > 1 x formulas............
> > 2 x fill formulas........
> > 3 x fill formulas........
> > 4
> > 5
> > 6 x end formula fill....
> > 7
> > 8
> > 9
> > The copy and paste section of the macro is working fine, it's the autofil
> > to
> > an always changing end row that I'm having trouble with. Hope this makes
> > sense.
> >
> > This is what I have been using...maybe I'm not even close...
> >
> > lRow = Range("AG4").End(xlDown).Row
> > Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
> >
> > Thanks as always for your help!!
> > Kelley
>
>

Don, what is the difference between finding the last row as you are doing
here, lRow = cells(rows.count,"AG4").End(xlUP).Row, and as I usually do, like
this:
LstRow = [F65000].End(xlUp).Row? I see this method all the time, so it is
what I use in all my macros. Are there situations where your method would be
better? And if so, why? Just trying to understand. Thank you.

Greg

"Don Guillett" wrote:

>
> spaces??
> maybe
> '>>lRow = Range("AG4").End(xlDown).Row
> lRow = cells(rows.count,"AG4").End(xlUP).Row
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Kell2604" <(E-Mail Removed)> wrote in message
> news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
> > Hi Guys,
> >
> > I need some assistance with a macro. I need the macro to grab some
> > formulas
> > from a static range of cells (B2 - G2) and paste that formula in a static
> > range (B4 - G4) and then I need it to autofill to the last empty row (the
> > end
> > of this range is always changing or else I would use a static end range).
> > For example, today I might need it to fill all the way to B36 - G36 and
> > tomorrow I might need it to fill to B77 - G77. The end of the range is
> > determined by the column immediately preceeding the formulas. In my
> > example
> > above A36 or A77.
> >
> > A B C D E F G H
> > 1 x formulas............
> > 2 x fill formulas........
> > 3 x fill formulas........
> > 4
> > 5
> > 6 x end formula fill....
> > 7
> > 8
> > 9
> > The copy and paste section of the macro is working fine, it's the autofil
> > to
> > an always changing end row that I'm having trouble with. Hope this makes
> > sense.
> >
> > This is what I have been using...maybe I'm not even close...
> >
> > lRow = Range("AG4").End(xlDown).Row
> > Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
> >
> > Thanks as always for your help!!
> > Kelley
>
>

Should have been
> lRow = cells(rows.count,"AG").End(xlUP).Row
Yours only uses 65000 rows which isn't accurate if using xl2007

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Greg Snidow" <(E-Mail Removed)> wrote in message
news:ADC9D40E-2E29-4024-9163-(E-Mail Removed)...
> Don, what is the difference between finding the last row as you are doing
> here, and doing it like this?
>
> LstRow = [A65000].End(xlUp).Row
>
> Is one method better than the other in certain situations? And if so,
> why?
> Just trying to understand. Thanks
>
> Greg
>
> "Don Guillett" wrote:
>
>>
>> spaces??
>> maybe
>> '>>lRow = Range("AG4").End(xlDown).Row
>> lRow = cells(rows.count,"AG4").End(xlUP).Row
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Kell2604" <(E-Mail Removed)> wrote in message
>> news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
>> > Hi Guys,
>> >
>> > I need some assistance with a macro. I need the macro to grab some
>> > formulas
>> > from a static range of cells (B2 - G2) and paste that formula in a
>> > static
>> > range (B4 - G4) and then I need it to autofill to the last empty row
>> > (the
>> > end
>> > of this range is always changing or else I would use a static end
>> > range).
>> > For example, today I might need it to fill all the way to B36 - G36 and
>> > tomorrow I might need it to fill to B77 - G77. The end of the range is
>> > determined by the column immediately preceeding the formulas. In my
>> > example
>> > above A36 or A77.
>> >
>> > A B C D E F G H
>> > 1 x formulas............
>> > 2 x fill formulas........
>> > 3 x fill formulas........
>> > 4
>> > 5
>> > 6 x end formula fill....
>> > 7
>> > 8
>> > 9
>> > The copy and paste section of the macro is working fine, it's the
>> > autofil
>> > to
>> > an always changing end row that I'm having trouble with. Hope this
>> > makes
>> > sense.
>> >
>> > This is what I have been using...maybe I'm not even close...
>> >
>> > lRow = Range("AG4").End(xlDown).Row
>> > Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
>> >
>> > Thanks as always for your help!!
>> > Kelley
>>
>>

For one thing, 65000 is not the last row in a worksheet, even prior to
XL07, so if there were 65535 rows of data, your method would fail while
the first method would work.

Obviously, XL07/08 files have LOTS more than 65000 rows. The first
method will work on either file type.

Second, using the evaluate method as you're doing *can* be slightly more
inefficient than using Cells(). Won't make a jot of difference when run
once, but most people feel it's better coding practice.

Third, it's a lot easier to generalize the Cells() method. Instead of a
constant, the "AG4" part could be calculated, or specified in a
constant, making it much easier to change one calculation (or constant)
and have all Cells() methods adjust, rather than having to search the
project for each instance of "AG".

In article <0B65B3AD-1CD9-437E-A4C7-(E-Mail Removed)>,
Greg Snidow <(E-Mail Removed)> wrote:

> Don, what is the difference between finding the last row as you are doing
> here, lRow = cells(rows.count,"AG4").End(xlUP).Row, and as I usually do, like
> this:
> LstRow = [F65000].End(xlUp).Row? I see this method all the time, so it is
> what I use in all my macros. Are there situations where your method would be
> better? And if so, why? Just trying to understand. Thank you.

Sorry for the double posting. The first time I hit 'Post' I got some strange
error message, then my window closed.

"Greg Snidow" wrote:

> Don, what is the difference between finding the last row as you are doing
> here, lRow = cells(rows.count,"AG4").End(xlUP).Row, and as I usually do, like
> this:
> LstRow = [F65000].End(xlUp).Row? I see this method all the time, so it is
> what I use in all my macros. Are there situations where your method would be
> better? And if so, why? Just trying to understand. Thank you.
>
> Greg
>
> "Don Guillett" wrote:
>
> >
> > spaces??
> > maybe
> > '>>lRow = Range("AG4").End(xlDown).Row
> > lRow = cells(rows.count,"AG4").End(xlUP).Row
> >
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "Kell2604" <(E-Mail Removed)> wrote in message
> > news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
> > > Hi Guys,
> > >
> > > I need some assistance with a macro. I need the macro to grab some
> > > formulas
> > > from a static range of cells (B2 - G2) and paste that formula in a static
> > > range (B4 - G4) and then I need it to autofill to the last empty row (the
> > > end
> > > of this range is always changing or else I would use a static end range).
> > > For example, today I might need it to fill all the way to B36 - G36 and
> > > tomorrow I might need it to fill to B77 - G77. The end of the range is
> > > determined by the column immediately preceeding the formulas. In my
> > > example
> > > above A36 or A77.
> > >
> > > A B C D E F G H
> > > 1 x formulas............
> > > 2 x fill formulas........
> > > 3 x fill formulas........
> > > 4
> > > 5
> > > 6 x end formula fill....
> > > 7
> > > 8
> > > 9
> > > The copy and paste section of the macro is working fine, it's the autofil
> > > to
> > > an always changing end row that I'm having trouble with. Hope this makes
> > > sense.
> > >
> > > This is what I have been using...maybe I'm not even close...
> > >
> > > lRow = Range("AG4").End(xlDown).Row
> > > Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
> > >
> > > Thanks as always for your help!!
> > > Kelley
> >
> >

Oops
> lRow = cells(rows.count,"AG4").End(xlUP).Row
should be
lRow = cells(rows.count,"AG").End(xlUP).Row
It would only make a difference if your data had blanks in column AG

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Kell2604" <(E-Mail Removed)> wrote in message
news:904DD0FD-6C9D-4542-A6AC-(E-Mail Removed)...
> Hello Don,
>
> Thanks for your help. Just to confirm are you suggesting I swap out my
> original line lRow = Range("AG4").End(xlDown).Row with what you have
> below...
> lRow = cells(rows.count,"AG4").End(xlUP).Row. I tried that and am getting
> the error: application-defined or object-defined error.
>
> Or am I misunderstanding you?
>
> "Don Guillett" wrote:
>
>>
>> spaces??
>> maybe
>> '>>lRow = Range("AG4").End(xlDown).Row
>> lRow = cells(rows.count,"AG4").End(xlUP).Row
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Kell2604" <(E-Mail Removed)> wrote in message
>> news:8D89E34F-AD23-4B4A-B480-(E-Mail Removed)...
>> > Hi Guys,
>> >
>> > I need some assistance with a macro. I need the macro to grab some
>> > formulas
>> > from a static range of cells (B2 - G2) and paste that formula in a
>> > static
>> > range (B4 - G4) and then I need it to autofill to the last empty row
>> > (the
>> > end
>> > of this range is always changing or else I would use a static end
>> > range).
>> > For example, today I might need it to fill all the way to B36 - G36 and
>> > tomorrow I might need it to fill to B77 - G77. The end of the range is
>> > determined by the column immediately preceeding the formulas. In my
>> > example
>> > above A36 or A77.
>> >
>> > A B C D E F G H
>> > 1 x formulas............
>> > 2 x fill formulas........
>> > 3 x fill formulas........
>> > 4
>> > 5
>> > 6 x end formula fill....
>> > 7
>> > 8
>> > 9
>> > The copy and paste section of the macro is working fine, it's the
>> > autofil
>> > to
>> > an always changing end row that I'm having trouble with. Hope this
>> > makes
>> > sense.
>> >
>> > This is what I have been using...maybe I'm not even close...
>> >
>> > lRow = Range("AG4").End(xlDown).Row
>> > Range("AH4").AutoFill Destination:=Range("AH4:AM" & lRow)
>> >
>> > Thanks as always for your help!!
>> > Kelley
>>
>>

Guys...I'm still getting an error.

This formula works great if I'm just trying to autofil 1 column. I seem to
be having trouble with autofilling 5 consecutive columns. I could do it one
by one with my original code but that seems like a lot of unnecessary work.

This is what I have currently...with the updates suggested.
lRow = Cells(Rows.Count, "AG").End(xlUp).Row
Range("AG4").AutoFill Destination:=Range("AG4:AM" & lRow)

New error - AutoFill method of Range class failed.




"JE McGimpsey" wrote:

> For one thing, 65000 is not the last row in a worksheet, even prior to
> XL07, so if there were 65535 rows of data, your method would fail while
> the first method would work.
>
> Obviously, XL07/08 files have LOTS more than 65000 rows. The first
> method will work on either file type.
>
> Second, using the evaluate method as you're doing *can* be slightly more
> inefficient than using Cells(). Won't make a jot of difference when run
> once, but most people feel it's better coding practice.
>
> Third, it's a lot easier to generalize the Cells() method. Instead of a
> constant, the "AG4" part could be calculated, or specified in a
> constant, making it much easier to change one calculation (or constant)
> and have all Cells() methods adjust, rather than having to search the
> project for each instance of "AG".
>
> In article <0B65B3AD-1CD9-437E-A4C7-(E-Mail Removed)>,
> Greg Snidow <(E-Mail Removed)> wrote:
>
> > Don, what is the difference between finding the last row as you are doing
> > here, lRow = cells(rows.count,"AG4").End(xlUP).Row, and as I usually do, like
> > this:
> > LstRow = [F65000].End(xlUp).Row? I see this method all the time, so it is
> > what I use in all my macros. Are there situations where your method would be
> > better? And if so, why? Just trying to understand. Thank you.
>