Excel - VBA: Code is generating unexpected numbers (0)

Question

I'm stuck on this, I don't understand why the code adds those extra zeros in column D; What am I doing wrong?

last = Range("B" & Rows.Count).End(xlUp).Row

'Insert 3 columns on left. Add information in Row 1, add data in column D.
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Columns("E:E").Copy Destination:=Columns("C:C")
Columns("E:E").Select
Selection.Delete Shift:=xlToLeft
Range("D2:D2" & last).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C[6]"
Columns("D").Copy
Columns("D").PasteSpecial xlPasteValues
Range("D1") = Time
Range("D1").NumberFormat = "h:mm:ss"

Input:

Output:

`Range("D2:D2" & last)` is the cause. If last = say 3 that will reference `D2:D23` — chris neilsen, Nov 26 '20 at 03:59
Aside from that you should read [how to avoid Select](https://stackoverflow.com/q/10714251) — chris neilsen, Nov 26 '20 at 04:00

score 1 · Accepted Answer · answered Nov 26 '20 at 04:01

Avoid the Selection object. It's created as a medium of communication between the user and VBA via the screen. VBA has direct access to the Excel workbook and therefore doesn't need it. Please try this code.

Private Sub InsertThreeColumns()

    Dim Rl          As Long         ' last used row
    Dim Rng         As Range

    With Worksheets("NewTest")      ' change to suit
        Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Columns(2).Copy
        .Columns(1).Insert Shift:=xlToRight
        Application.CutCopyMode = False
        .Columns("A:B").Insert Shift:=xlToRight

        .Columns(5).EntireColumn.Delete
        On Error Resume Next
        ' next line will cause a crash if there are no blanks
        Set Rng = .Range(.Cells(2, "D"), .Cells(Rl, "D")).SpecialCells(xlCellTypeBlanks)
        If Err = 0 Then
            Rng.FormulaR1C1 = "=R[-1]C[6]"
            Rng.Copy
            Rng.PasteSpecial xlPasteValues
        End If
        On Error GoTo 0
        With .Cells(1, "D")
            .Value = Time
            .NumberFormat = "h:mm:ss"
        End With
    End With
End Sub

The extra zeroes that troubled you were caused by the method of defining the range where you wanted the formula to supply cell content. In your code that range is a derivative of a Selection. In the above code it's defined by starting cell and end cell. The result should be the same. It isn't because the route via the Selection object is circuitous and difficult to follow.

Excel - VBA: Code is generating unexpected numbers (0)

1 Answers1