1st Excel Macro -- Reference to a specific cell not wanted

[FamilyGuy902]

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

 

[Don Guillett]

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

 

[Gord Dibben]

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

 

[FamilyGuy902]

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

 

[FamilyGuy902]

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

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

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