Pasting into a column

[dhoward via OfficeKB.com]

I download a lot of data from various sources and need to manipulate in Excel.
These spreadsheets are of variable size. Sometimes I need to add a formula
to the spreadsheet. I've been entering my formula, copying it and then
selecting entire column and then pasting it. This of course expands the size
of my spreadsheet to the maximum size. I tried using paste special but that
didn't prevent it. What can I try to paste only in the active area of my
sheet?

 

[CLR]

Enter your formula in the uppermost cell in the column of choice, then
left-click on it and left-click and drag the little black square in the lower
right hand corner down the column as far as you wish to copy the formula...

hth
Vaya con Dios,
Chuck, CABGx3

 

[dhoward via OfficeKB.com]

Thanks for the suggestion. This works great when it is just a one time use.
However, I do this a lot in macros and find that using auto fill doesn't work
perfectly in a merge. Any other ideas?

Enter your formula in the uppermost cell in the column of choice, then
left-click on it and left-click and drag the little black square in the lower
right hand corner down the column as far as you wish to copy the formula...

hth
Vaya con Dios,
Chuck, CABGx3

I download a lot of data from various sources and need to manipulate in Excel.
These spreadsheets are of variable size. Sometimes I need to add a formula

[quoted text clipped - 3 lines]

didn't prevent it. What can I try to paste only in the active area of my
sheet?

 

[CLR]

Tom Ogilvy gave me this code one time when I had what I think was a similar
problem..........perhaps it's what you're looking for.......

Range("i8").Formula = "=H8 + G8"
Range("i8").Select
Selection.AutoFill Destination:=Range("i8:i" & _
cells(rows.count,8).End(xlup).Row) '8 stands for column H

hth
Vaya con Dios,
Chuck, CABGx3

Thanks for the suggestion. This works great when it is just a one time use.
However, I do this a lot in macros and find that using auto fill doesn't work
perfectly in a merge. Any other ideas?

Enter your formula in the uppermost cell in the column of choice, then
left-click on it and left-click and drag the little black square in the lower
right hand corner down the column as far as you wish to copy the formula...

hth
Vaya con Dios,
Chuck, CABGx3

I download a lot of data from various sources and need to manipulate in Excel.
These spreadsheets are of variable size. Sometimes I need to add a

formula
[quoted text clipped - 3 lines]

didn't prevent it. What can I try to paste only in the active area of my
sheet?

 

[dhoward via OfficeKB.com]

Thanks, I just tried to enter this as a macro but I'm getting a syntex error
message on this part. I've entered it just as you have it below.
Selection.AutoFill Destination:=Range("i8:i" & _
cells(rows.count,8).End(xlup).Row)

Tom Ogilvy gave me this code one time when I had what I think was a similar
problem..........perhaps it's what you're looking for.......

Range("i8").Formula = "=H8 + G8"
Range("i8").Select
Selection.AutoFill Destination:=Range("i8:i" & _
cells(rows.count,8).End(xlup).Row) '8 stands for column H

hth
Vaya con Dios,
Chuck, CABGx3

Thanks for the suggestion. This works great when it is just a one time use.
However, I do this a lot in macros and find that using auto fill doesn't work

[quoted text clipped - 13 lines]

 

[CLR]

I just tried it again, just as I posted, with the Range selection line
before the AutoFill line.......worked fine on my XL97. It does fail with no
Range Selection line in place.......Do you have a Range Selection line in
place?........perhaps if you posted your whole macro we could see the
problem.

Vaya con Dios,
Chuck, CABGx3

Thanks, I just tried to enter this as a macro but I'm getting a syntex error
message on this part. I've entered it just as you have it below.
Selection.AutoFill Destination:=Range("i8:i" & _
cells(rows.count,8).End(xlup).Row)

Tom Ogilvy gave me this code one time when I had what I think was a similar
problem..........perhaps it's what you're looking for.......

Range("i8").Formula = "=H8 + G8"
Range("i8").Select
Selection.AutoFill Destination:=Range("i8:i" & _
cells(rows.count,8).End(xlup).Row) '8 stands for column H

hth
Vaya con Dios,
Chuck, CABGx3

Thanks for the suggestion. This works great when it is just a one time use.
However, I do this a lot in macros and find that using auto fill

doesn't work
[quoted text clipped - 13 lines]

didn't prevent it. What can I try to paste only in the active area of my
sheet?

 

[dhoward via OfficeKB.com]

Thanks for the response. I believe I had a Range Selection line. (I'm not
too experienced with complex macros.) Here's the macro as I typed it:

Range("I8").Formula = "=H8+G8"
Range("I8").Select
Selection.AutoFill Destination:=Range ("I8:I" &_Cells(Rows.Count,8).End
(xlUp).Row)
End Sub

When I edit the macro, the line that starts with Selection...is in a red font
which leads me to believe there's an error here.

I just tried it again, just as I posted, with the Range selection line
before the AutoFill line.......worked fine on my XL97. It does fail with no
Range Selection line in place.......Do you have a Range Selection line in
place?........perhaps if you posted your whole macro we could see the
problem.

Vaya con Dios,
Chuck, CABGx3

Thanks, I just tried to enter this as a macro but I'm getting a syntex error
message on this part. I've entered it just as you have it below.

[quoted text clipped - 18 lines]

 

[CLR]

The problem is in the word wrapping .........

The line should read, all on one line.......

Selection.AutoFill Destination:=Range ("I8:I"
&Cells(Rows.Count,8).End(xlUp).Row)

or, this would be acceptable........

Selection.AutoFill Destination:=Range ("I8:I" & _
Cells(Rows.Count,8).End(xlUp).Row)

Notice the space and underscore at the end of the first line......that
connects the two lines together in VBA........

Vaya con Dios,
Chuck, CABGx3

Thanks for the response. I believe I had a Range Selection line. (I'm not
too experienced with complex macros.) Here's the macro as I typed it:

Range("I8").Formula = "=H8+G8"
Range("I8").Select
Selection.AutoFill Destination:=Range ("I8:I" &_Cells(Rows.Count,8).End
(xlUp).Row)
End Sub

When I edit the macro, the line that starts with Selection...is in a red font
which leads me to believe there's an error here.

I just tried it again, just as I posted, with the Range selection line
before the AutoFill line.......worked fine on my XL97. It does fail with no
Range Selection line in place.......Do you have a Range Selection line in
place?........perhaps if you posted your whole macro we could see the
problem.

Vaya con Dios,
Chuck, CABGx3

Thanks, I just tried to enter this as a macro but I'm getting a syntex error
message on this part. I've entered it just as you have it below.

[quoted text clipped - 18 lines]

didn't prevent it. What can I try to paste only in the active area of my
sheet?

 

[dhoward via OfficeKB.com]

Thank you! It worked.

The problem is in the word wrapping .........

The line should read, all on one line.......

Selection.AutoFill Destination:=Range ("I8:I"
&Cells(Rows.Count,8).End(xlUp).Row)

or, this would be acceptable........

Selection.AutoFill Destination:=Range ("I8:I" & _
Cells(Rows.Count,8).End(xlUp).Row)

Notice the space and underscore at the end of the first line......that
connects the two lines together in VBA........

Vaya con Dios,
Chuck, CABGx3

Thanks for the response. I believe I had a Range Selection line. (I'm not
too experienced with complex macros.) Here's the macro as I typed it:

[quoted text clipped - 22 lines]

 

[CLR]

You're welcome.........thanks for the feedback

Vaya con Dios,
Chuck, CABGx3

Thank you! It worked.

The problem is in the word wrapping .........

The line should read, all on one line.......

Selection.AutoFill Destination:=Range ("I8:I"
&Cells(Rows.Count,8).End(xlUp).Row)

or, this would be acceptable........

Selection.AutoFill Destination:=Range ("I8:I" & _
Cells(Rows.Count,8).End(xlUp).Row)

Notice the space and underscore at the end of the first line......that
connects the two lines together in VBA........

Vaya con Dios,
Chuck, CABGx3

Thanks for the response. I believe I had a Range Selection line. (I'm not
too experienced with complex macros.) Here's the macro as I typed it:

[quoted text clipped - 22 lines]

didn't prevent it. What can I try to paste only in the active area of my
sheet?