x
Solved

# Copy a formula in A2 down column.

Posted on 2013-12-20
Medium Priority
555 Views
What is the VBA code to copy a formula down a column (is case A) starting at A2, and copying it down as long as there is a value in the cell to its right?  Then after that Copy>SpecialPaste>Values and number formats, so the formula is gone?

Assuming it starts with A2 being the active cell.

0
Question by:RWayneH
• 2

LVL 55

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39732298
Hi,

pls try
``````Set myRange = Range(Range("A2"), Range("A" & Range("B2").End(xlDown).Row))
Range("A2").AutoFill Destination:=myRange
myRange.Copy
myRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
``````
Regards
0

LVL 35

Expert Comment

ID: 39732306
RWH,

You're likely going to get a few methods of doing the same. Here's mine:
`````` With Range("A2")
If Len(.Offset(0, 1).Value) > 0 And Len(.Offset(1, 1).Value) > 0 Then
.Copy .Resize(.Offset(0, 1).End(xlDown).Row - .Row + 1, 1)
.Resize(.Offset(0, 1).End(xlDown).Row - .Row + 1, 1).Value = .Resize(.Offset(0, 1).End(xlDown).Row - .Row + 1, 1).Value
End If
End With
``````
Matt
0

Author Closing Comment

ID: 39733162
EXCELent!!  -R-
0

LVL 35

Expert Comment

ID: 39733178
EXCELent!!  -R-
:D

By the way, if this macro might ever be run when there is only one row of data in the column next to it, it will fill the formula down through every row in the sheet (1m+). If that is even a possibility, you may want to add something to check for that. If it is only run manually, and you wouldn't do it with only one row, then there's no need.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.