Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Copy a formula in A2 down column.

Posted on 2013-12-20
Medium Priority
545 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 52

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.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…