Have Excel macro populate only desired cells

Question

I am currently using a piece of VBA code to auto-populate blank cells in column C of my report. The only thing is it will autopopulate past the end of my data set. (There are several columns of data in this report.) In order to stop the VBA code, I have set the range up to C1500. I would prefer it if that macro would just automatically stop once it reached rows with no data in them. Does any one know how to change my code to accomplish this?

Sub Macro1()
    mynumber = 1
    Dim r As Range, cell As Range
    Set r = Range(Range("C2"), Range("C1500"))
    For Each cell In r
        If cell.Value = OK Then cell.Value = mynumber
        mynumber = mynumber + 1
    Next
End Sub

Instead of looping to row `1500`, [find the last row](https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row) in your sheet — cybernetic.nomad, Feb 27 '19 at 21:46
You also need to add an `End if` to close out your `If` statement — Darrell H, Feb 27 '19 at 21:51
@DarrellH No, that's a single line `If` statement. It's syntactically correct. — Comintern, Feb 27 '19 at 21:54

score 0 · Answer 1 · answered Feb 27 '19 at 22:21

0

Change

Set r = Range(Range("C2"), Range("C1500"))

to

Set r = Range(Range("C2"), Range("C" & Cells(Rows.Count, "C").End(xlUp).Row))

answered Feb 27 '19 at 22:21

dwirony

5,487
3
21
43

Have Excel macro populate only desired cells

1 Answers1