Excel2003

I have data in column A. Data is in groups of three so I can use formulas to
copy into cells in columns B, C, and D; and have been doing so for quite
awhile. But it is time consuming to do this every time. So I need help
automating:

Given:
A1 B1=A1 C1=A2 D1=A3
A2
A3
~
A11998 B11998=A11998 C11998=A11999 D11998=A12000
A11999
A12000

I highlight and copy B1 through D3, then double-tap the small square in the
bottom right corner of the highlighted cells to copy down. The end record
isn't always the same. Sometimes just a few hundred cells, once over 20,000
cells.
When I tried to write a macro to do the same thing, it went all the way down
to A65536.
Please help me to automate this so the copy down process stops when it
reaches the last entry in column A, not the last cell.
--
Kat

Hi Kat,

There are better ways of writing the code but the following method should
work and will probably help you for future reference.

When you want to record a macro to copy down to the last cell containing
data, set the recorder then select the cell, then copy, hold Ctrl and Shift
keys down and press down arrow then paste.

The recorded code will copy to the last cell containing data irrespective of
how many rows. Note that it does not matter that the selected range for paste
includes the cell copied.

--
Regards,

OssieMac


"Kat" wrote:

Excel2003

I have data in column A. Data is in groups of three so I can use formulas to
copy into cells in columns B, C, and D; and have been doing so for quite
awhile. But it is time consuming to do this every time. So I need help
automating:

Given:
A1 B1=A1 C1=A2 D1=A3
A2
A3
~
A11998 B11998=A11998 C11998=A11999 D11998=A12000
A11999
A12000

I highlight and copy B1 through D3, then double-tap the small square in the
bottom right corner of the highlighted cells to copy down. The end record
isn't always the same. Sometimes just a few hundred cells, once over 20,000
cells.
When I tried to write a macro to do the same thing, it went all the way down
to A65536.
Please help me to automate this so the copy down process stops when it
reaches the last entry in column A, not the last cell.
--
Kat

My apologies OssieMac. I must be incredibly dense. When I tried your method,
here is what recorded:

Sub CopyDown()
'
' CopyDown Macro
' Macro recorded 10/6/2009

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End Sub

First: After I inserted the formulas I then select and copy the cells, then
I use SHIFT/CTRL DOWN-ARROW, and it goes all the way to the bottom of the
sheet instead of stopping next to the last row used. So this did not work.
Second: Although entire range is highlighted, when I select paste, only the
original selection of B1:D3 is pasted. The remainder are blank cells.

So this method isn't working for me. Am I missing something?

Using my original method gives me the following results:

Sub CopyDown2()
'
' CopyDown2 Macro
' Macro recorded 10/6/2009

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B1:D150")
Range("B1:D150").Select
End Sub

I am missing something. Please help.
--
Kat


"OssieMac" wrote:

Hi Kat,

There are better ways of writing the code but the following method should
work and will probably help you for future reference.

When you want to record a macro to copy down to the last cell containing
data, set the recorder then select the cell, then copy, hold Ctrl and Shift
keys down and press down arrow then paste.

The recorded code will copy to the last cell containing data irrespective of
how many rows. Note that it does not matter that the selected range for paste
includes the cell copied.

--
Regards,

OssieMac


"Kat" wrote:

Excel2003

I have data in column A. Data is in groups of three so I can use formulas to
copy into cells in columns B, C, and D; and have been doing so for quite
awhile. But it is time consuming to do this every time. So I need help
automating:

Given:
A1 B1=A1 C1=A2 D1=A3
A2
A3
~
A11998 B11998=A11998 C11998=A11999 D11998=A12000
A11999
A12000

I highlight and copy B1 through D3, then double-tap the small square in the
bottom right corner of the highlighted cells to copy down. The end record
isn't always the same. Sometimes just a few hundred cells, once over 20,000
cells.
When I tried to write a macro to do the same thing, it went all the way down
to A65536.
Please help me to automate this so the copy down process stops when it
reaches the last entry in column A, not the last cell.
--
Kat

Woohoo! I found a solution in another of your thread replies. The answer was
staring me in the face & I didn't see it.

Here's the solution based on your input:

Sub TestMacro()

Dim lastRow As Long
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"

'Edit the "A" in the following line to match
'the column to test for last row
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:D3").Select
Selection.AutoFill Destination:=Range("B1:D" & lastRow),
Type:=xlFillDefault
Range("B1:D" & lastRow).Select

End Sub


--
Kat


"Kat" wrote:

My apologies OssieMac. I must be incredibly dense. When I tried your method,
here is what recorded:

Sub CopyDown()
'
' CopyDown Macro
' Macro recorded 10/6/2009

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End Sub

First: After I inserted the formulas I then select and copy the cells, then
I use SHIFT/CTRL DOWN-ARROW, and it goes all the way to the bottom of the
sheet instead of stopping next to the last row used. So this did not work.
Second: Although entire range is highlighted, when I select paste, only the
original selection of B1:D3 is pasted. The remainder are blank cells.

So this method isn't working for me. Am I missing something?

Using my original method gives me the following results:

Sub CopyDown2()
'
' CopyDown2 Macro
' Macro recorded 10/6/2009

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B1:D150")
Range("B1:D150").Select
End Sub

I am missing something. Please help.
--
Kat

Hi Kat,

Pleased that you have it sorted. Gives a feeling of euphoria when you work
it out and beat the system.

Anyway my apologies. It was a misunderstanding on my part. I thought that
you had data in the columns and was overwriting it. From your description now
that was obviously not the case.

Happy programming.

--
Regards,

OssieMac


"Kat" wrote:

Woohoo! I found a solution in another of your thread replies. The answer was
staring me in the face & I didn't see it.

Here's the solution based on your input:

Sub TestMacro()

Dim lastRow As Long
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"

'Edit the "A" in the following line to match
'the column to test for last row
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B1:D3").Select
Selection.AutoFill Destination:=Range("B1:D" & lastRow),
Type:=xlFillDefault
Range("B1:D" & lastRow).Select

End Sub


--
Kat


"Kat" wrote:

My apologies OssieMac. I must be incredibly dense. When I tried your method,
here is what recorded:

Sub CopyDown()
'
' CopyDown Macro
' Macro recorded 10/6/2009

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End Sub

First: After I inserted the formulas I then select and copy the cells, then
I use SHIFT/CTRL DOWN-ARROW, and it goes all the way to the bottom of the
sheet instead of stopping next to the last row used. So this did not work.
Second: Although entire range is highlighted, when I select paste, only the
original selection of B1:D3 is pasted. The remainder are blank cells.

So this method isn't working for me. Am I missing something?

Using my original method gives me the following results:

Sub CopyDown2()
'
' CopyDown2 Macro
' Macro recorded 10/6/2009

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=R[1]C[-2]"
Range("D1").Select
ActiveCell.FormulaR1C1 = "=R[2]C[-3]"
Range("B1:D3").Select
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B1:D150")
Range("B1:D150").Select
End Sub

I am missing something. Please help.
--
Kat