I have recorded an excel Macro. I am trying to paste a function down
for as many rows that are in my file. However, as shown in the
following code, it is making reference to cell C2926, which happens to
be the last row in the file that I used to record the macro. It causes
my macro to crash if there are a different amount of rows in my file.

Selection.End(xlDown).Select
Range("C2926").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

Does anyone know what the proper code should be, or what sequence of
commands I should do when recording the macro? I thought by doing
end->down arrow would be the way to go, but I guess not....

Thanks,
Jason

You didn't post it all but you want to remove selections whereever possible

lr = Cells(Rows.Count, "c").End(xlUp).Row
With Range("c2:c" & lr)
..FillDown
..Value = .Value 'change formula to value NO overhead
End With

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"FamilyGuy902" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have recorded an excel Macro. I am trying to paste a function down
> for as many rows that are in my file. However, as shown in the
> following code, it is making reference to cell C2926, which happens to
> be the last row in the file that I used to record the macro. It causes
> my macro to crash if there are a different amount of rows in my file.
>
> Selection.End(xlDown).Select
> Range("C2926").Select
> Range(Selection, Selection.End(xlUp)).Select
> ActiveSheet.Paste
>
> Does anyone know what the proper code should be, or what sequence of
> commands I should do when recording the macro? I thought by doing
> end->down arrow would be the way to go, but I guess not....
>
> Thanks,
> Jason
>

If you are sure there are no blanks in column C...........

Sub selectrange2()
Range(Range("C1"), Range("C1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End Sub

If there may be blanks work from the selected cell to the bottom of sheet then
up to data.............

Sub selectrange3()
Range("C1").Select
Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Copy _
Destination:=Sheets("Sheet2").Range("A1")
End Sub


Gord Dibben MS Excel MVP

On 16 Nov 2006 12:32:42 -0800, "FamilyGuy902" <(E-Mail Removed)> wrote:

>I have recorded an excel Macro. I am trying to paste a function down
>for as many rows that are in my file. However, as shown in the
>following code, it is making reference to cell C2926, which happens to
>be the last row in the file that I used to record the macro. It causes
>my macro to crash if there are a different amount of rows in my file.
>
> Selection.End(xlDown).Select
> Range("C2926").Select
> Range(Selection, Selection.End(xlUp)).Select
> ActiveSheet.Paste
>
>Does anyone know what the proper code should be, or what sequence of
>commands I should do when recording the macro? I thought by doing
>end->down arrow would be the way to go, but I guess not....
>
>Thanks,
>Jason

Thanks for the responses. I've tried to incorporate your code into
mine, but I keep getting errors. I don't know visual basic, so I'm not
good at editing it. I've re-recorded the macro, and I'm including the
entire code here. Can someone please take a stab at editing my code.
Thanks!!!

Sub NewMacro()

Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("C2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"","",RC[-1])"
Selection.Copy
Range("B2").Select
Selection.End(xlDown).Select
Range("C2926").Select ' ***HERE IS THE PROBLEM***
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("C1").Select
ActiveCell.FormulaR1C1 = "New Name"
End Sub




Gord Dibben wrote:
> If you are sure there are no blanks in column C...........
>
> Sub selectrange2()
> Range(Range("C1"), Range("C1").End(xlDown)).Copy _
> Destination:=Sheets("Sheet2").Range("A1")
> End Sub
>
> If there may be blanks work from the selected cell to the bottom of sheet then
> up to data.............
>
> Sub selectrange3()
> Range("C1").Select
> Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Copy _
> Destination:=Sheets("Sheet2").Range("A1")
> End Sub
>
>
> Gord Dibben MS Excel MVP
>
> On 16 Nov 2006 12:32:42 -0800, "FamilyGuy902" <(E-Mail Removed)> wrote:
>
> >I have recorded an excel Macro. I am trying to paste a function down
> >for as many rows that are in my file. However, as shown in the
> >following code, it is making reference to cell C2926, which happens to
> >be the last row in the file that I used to record the macro. It causes
> >my macro to crash if there are a different amount of rows in my file.
> >
> > Selection.End(xlDown).Select
> > Range("C2926").Select
> > Range(Selection, Selection.End(xlUp)).Select
> > ActiveSheet.Paste
> >
> >Does anyone know what the proper code should be, or what sequence of
> >commands I should do when recording the macro? I thought by doing
> >end->down arrow would be the way to go, but I guess not....
> >
> >Thanks,
> >Jason

Hello. Could someone who understands Visual Basic take a stab at
helping me with this. Thanks in advance.


FamilyGuy902 wrote:
> Thanks for the responses. I've tried to incorporate your code into
> mine, but I keep getting errors. I don't know visual basic, so I'm not
> good at editing it. I've re-recorded the macro, and I'm including the
> entire code here. Can someone please take a stab at editing my code.
> Thanks!!!
>
> Sub NewMacro()
>
> Columns("B:B").Select
> Selection.Insert Shift:=xlToRight
> Selection.Insert Shift:=xlToRight
> Columns("A:A").Select
> Selection.TextToColumns Destination:=Range("A1"),
> DataType:=xlDelimited, _
> TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
> Tab:=False, _
> Semicolon:=False, Comma:=True, Space:=False, Other:=False,
> FieldInfo _
> :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
> Range("C2").Select
> ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],"","",RC[-1])"
> Selection.Copy
> Range("B2").Select
> Selection.End(xlDown).Select
> Range("C2926").Select ' ***HERE IS THE PROBLEM***
> Range(Selection, Selection.End(xlUp)).Select
> ActiveSheet.Paste
> Columns("C:C").Select
> Application.CutCopyMode = False
> Selection.Copy
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Range("C1").Select
> ActiveCell.FormulaR1C1 = "New Name"
> End Sub
>
>
>
>
> Gord Dibben wrote:
> > If you are sure there are no blanks in column C...........
> >
> > Sub selectrange2()
> > Range(Range("C1"), Range("C1").End(xlDown)).Copy _
> > Destination:=Sheets("Sheet2").Range("A1")
> > End Sub
> >
> > If there may be blanks work from the selected cell to the bottom of sheet then
> > up to data.............
> >
> > Sub selectrange3()
> > Range("C1").Select
> > Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Copy _
> > Destination:=Sheets("Sheet2").Range("A1")
> > End Sub
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On 16 Nov 2006 12:32:42 -0800, "FamilyGuy902" <(E-Mail Removed)> wrote:
> >
> > >I have recorded an excel Macro. I am trying to paste a function down
> > >for as many rows that are in my file. However, as shown in the
> > >following code, it is making reference to cell C2926, which happens to
> > >be the last row in the file that I used to record the macro. It causes
> > >my macro to crash if there are a different amount of rows in my file.
> > >
> > > Selection.End(xlDown).Select
> > > Range("C2926").Select
> > > Range(Selection, Selection.End(xlUp)).Select
> > > ActiveSheet.Paste
> > >
> > >Does anyone know what the proper code should be, or what sequence of
> > >commands I should do when recording the macro? I thought by doing
> > >end->down arrow would be the way to go, but I guess not....
> > >
> > >Thanks,
> > >Jason