Auto-fill Range to Last Row Used

Question

I have 0 in cell G1 and need to autofill with 0s till the last used row.

In the below code the 0s are updating till row 30 while my rows are only filled till row 4.

I want the 0s to fill only if there's data in the adjacent row.

NumberConv is Sheet2 in my workbook.

Sub MacroNC

    Sheets("NumberConv").Select
    Range("G1").Select
    Selection.NumberFormat = "@"
    ActiveCell.FormulaR1C1 = "0"
    
    Sheet2.Select
    Range("G1").Select
    lastRow = ActiveSheet.UsedRange.Rows.Count
    Selection.AutoFill Destination:=Range("G1:G" & lastRow), Type:=xlFillCopy

End Sub

Any specific reason for `.NumberFormat = "@"`? – Siddharth Rout Mar 02 '21 at 21:18 — Siddharth Rout, Mar 02 '21 at 21:18

BigBen · Accepted Answer · 2021-03-02T21:18:17.037

1

UsedRange is unreliable; use End(xlUp) to find the last row.
No need to AutoFill, you can apply a NumberFormat and write a Value to an entire range.
No Need to Select.

Sub MacroNC
    With Sheet2
        Dim lastRow As Long
        lastRow = .Cells(.Rows.Count, "F").End(xlUp).Row

        .Range("G1:G" & lastRow).NumberFormat = "@"
        .Range("G1:G" & lastRow).Value = 0
    End With
End Sub

Side note: Unclear why you need to store the zeros as text.

edited Mar 02 '21 at 21:18

answered Mar 02 '21 at 21:07

BigBen

46,229
7
24
40

Thanks @BigBen, you the man. I am needing it as a text as the system I am transferring the data to for some reason wants it as a text :/ – SumerOf94 Mar 02 '21 at 21:29
I supposed that might be the reason... well I'm sorry you have to work with such a dumb system. – BigBen Mar 02 '21 at 21:30

Auto-fill Range to Last Row Used

1 Answers1