We help IT Professionals succeed at work.

Excel 2010 - Copy a Formula to every other cell in a column

mmj1
mmj1 asked
on
I have an Excel spreadsheet that has a formula in cell C3 -- I would like to copy that formula down column C but in every other cell -- C5, C7, C9, etc.  Is there any easy way to do this?  Thank you
Comment
Watch Question

Hi

yes there is.

If your C3 as a formula in it and that C4 doesn't,  you can select C3 and C4, and use the drag square option to drag the formula every 2 rows.

Ex:
Select the 2 cells C3 and C4 and drag the small square down.  (see below picture)
Drag 1
Go down with the drag
drag 2
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sub EveryOtherCell()

Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("C1048576").End(xlUp).Row

Range("C3").Select
    Selection.Copy
'lngLastRow here can be change to any specific range/cell in column C
For lngRow = 5 To lngLastRow Step 2
   ' Cells(lngRow, 3).Formula = Range("C3").Formula
    Cells(lngRow, 3).Activate
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
Next

End Sub

Open in new window

So you have 2 options, VBA macro or a drag formula.

Author

Commented:
That worked great!!!  Thank you.

Author

Commented:
Thank you!
I'm glad i was able to help.