Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VBA Special Cells by Column

Posted on 2013-02-06
2
Medium Priority
?
510 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

571 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