Autofill Macro

  • Thread starter Thread starter Kat
  • Start date Start date
K

Kat

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.
 
Perhaps there may be more elegant solutions

try this macro

Code: 
Sub test()
 Dim r As Range, r1 As Range
 
 Set r = Range("A1")
 Do
 'MsgBox r.Address
 If r = "" Then Exit Sub
 Set r1 = Range(r, r.Offset(2, 0))
 'MsgBox r1.Address
 r1.Copy
 r.Offset(0, 1).PasteSpecial , Transpose:=True
 Set r = r.Offset(3, 0)
 Loop
 End Sub
 
Last edited:
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.
 
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.
 
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
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top