CODEWITHSUNDEEP
excelvba
Econ502
Econ502

Reputation: 19

How do I AutoFill down in one column, using the row count of another column?

I am trying to produce some code that will use the AutoFill Down function. I would like to fill Column B with the typical 1,2,3,4, etc. as long as there is text/values in the respective rows of Column A. Right now, my code is hardwired to fill down to cell B50 no matter what, but I don't want it to paste that far down if Column A only has data through cell A7, for example. Ideally, I would like to use the following variable -- rownum = Range("A1").End(xlDown).Row -- to count the number of cells in Column A that have text/values, and use that to replace "B50" in the row designation below. Just not sure of the appropriate syntax to make that happen. Here is the code I have so far.

ActiveCell.FormulaR1C1 = "1"
Range("B2").Select
ActiveCell.FormulaR1C1 = "2"
Range("A1").Select
Range("B1:B2").Select
Selection.AutoFill Destination:=Range("B1:B50"), Type:=xlFillDefault

Thanks in advance to anyone who helps me out! I am a new user of both Macros and VBA Code, and the amount of knowledge that so many of you have amazes me!

Econ

Upvotes: 1

Views: 31765

Answers (2)

Frank
Frank

Reputation: 11

Pretty simple option to use without the need for a string:

Selection.AutoFill Destination:=Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row)

Same goes for copying the number of rows in a column:

Range("A2:A" & Cells(Rows.Count, "B").End(xlUp).Row).Select
Selection.Copy

Upvotes: 1

Jon Crowell
Jon Crowell

Reputation: 22320

You're much better off if you don't select anything in your code. If you just want to number the rows in column B based on the contents of column A, this will do the trick:

Sub NumberColumn()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' get the last row from column A that has a value
    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    ' use the last row to determine how far down to extend the formula
    ws.Range("B1:B" & lastRow).Formula = "=row()"
End Sub

Upvotes: 4

Related Questions