Solved

Excel VBA Special Cells by Column

Posted on 2013-02-06
2
491 Views
Last Modified: 2013-02-06
I need to fill in blank spaces in column C in a spreadsheet.  The loop should start from the bottom of column C and step up. If the next cell up is blank, I need to copy and paste from the cell below.  The problem I'm having is my code selects all the blank cells and copies from the cell below. I just want this copying to happen in column C.
What am I missing?

Sub Macro2()

Dim LastRow As Long
Dim i As Long
 
 Application.ScreenUpdating = False
 
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
     For i = LastRow To 1 Step -1
       Columns("C:C").Select
            Selection.SpecialCells(xlCellTypeBlanks).Select
            Selection.FormulaR1C1 = "=R[+1]C"    
        Next i
Application.ScreenUpdating = True

End Sub

Open in new window

0
Comment
Question by:minamina6
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 38859777
Without looping try

Range("C1:C" & lastrow).specialcells(xlcelltypeblanks).formular1c1= "=R[+1]C"
0
 

Author Closing Comment

by:minamina6
ID: 38859807
That worked. Thanks!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question