How do I make the cell Range work in VBA?

Question

I have a problem with the autofill function. I want to use the macro to fill in the date until there is nothing left in B. The problem is that there are some gaps there. Can I change the code so that it fills up to the last line in B. I tried it with the code below. However, it does not work.

Sub fill()
    Sheets("Table1").Select
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""",R[-1]C,RC[-1])"
    ActiveCell.Select
    Dim last As Long
    Range("C2").Select
    Range(Selection, Selection.End(xlToRight)).AutoFill Destination:=Range("C2:C" & last)
    Selection.End(xlDown).Select
    Selection.ClearContents
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
End Sub

score 4 · Accepted Answer · answered Jul 08 '20 at 15:02

You do not need such a lengthy process. You can do it in just couple of lines. For example

rng.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Here is an example. Assuming that your range is from C2 onwards as shown below.

Try this code

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Set ws = Sheets("Table")
    
    Dim lRow As Long
    With ws
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row
        
        Dim rng As Range
        Set rng = .Range("C3:C" & lRow)
        
        Dim visibleCells As Range
        On Error Resume Next
        Set visibleCells = rng.SpecialCells(xlCellTypeBlanks)
        On Error GoTo 0
        
        If Not visibleCells Is Nothing Then
            visibleCells.FormulaR1C1 = "=R[-1]C"
        End If
    End With
End Sub

In Action

Worth a Mention

Also avoid using Select/Selection/Activecell etc. You may want to read up on How to avoid using Select in Excel VBA
You do not need VBA to achieve what you want. You can achieve the same using few clicks.

NON VBA Method

Select your range. In this case C3:C13
Press CTRL + G to bring up the Go To dialog box
Click Special button
Select the Blanks radio button and click OK.
In the Formula bar type =C2 and press CTRL + ENTER key and you are done

In Action

I was about to describe your Non VBA method: I learnt it about 20 years ago during a course and since then, I've never stopped being surprised about the power of MS-Excel :-) — Dominique, Jul 08 '20 at 15:07
Wow! Thank you so much! Works perfectly, thanks for the one with how to avoid selection as well :) — Joshua Scholz, Jul 09 '20 at 08:05

How do I make the cell Range work in VBA?

1 Answers1