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

Answer 1

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

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

NON VBA Method

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

In Action