CODEWITHSUNDEEP
excelvba
tmdgus
tmdgus

Reputation: 1

Auto-Increment Dynamic Range

I have a column that increments cell value by 1 in every row using VBA. (e.g. A1: 22000, A2: 22001, A3:22003).
However, in my code I can only auto-increment cell values until a specific Range. Can I make this range dynamic? I looked for autofill dynamic range tutorials and when I tried it, nothing changed at all. I am quite new to VBA so would be grateful if anyone could help.

Here's my code:

Sub IncrementDyn()
    Range("A1:A2").AutoFill Destination:= Range("A1:A10"), Type:= xlFillDefault
End Sub

I tried to apply Dynamic Range to my code like this but failed:

Sub IncrementDyn()
    Range("A1:A2").AutoFill Destination:= Range("A1:A" & Range("A"&Rows.Count).End(xlUp).Row), Type:= xlFillDefault
End Sub

Upvotes: 0

Views: 159

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

I cannot reproduce your issue.

Having this: enter image description here

ends up in this enter image description here

And the following enter image description here

Ends up in enter image description here

Just using

Option Explicit

Public Sub IncrementDyn()
    Range("A1:A2").AutoFill Destination:=Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault
End Sub

Maybe make sure you do this on the right sheet by specifying the sheet explicitly.

Option Explicit

Public Sub IncrementDyn()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    ws.Range("A1:A2").AutoFill Destination:=ws.Range("A1:A" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row), Type:=xlFillDefault
End Sub

You should never use Range, Cells, Rows and Columns objects without specifying their workbook and worksheet. Otherwise Excel defaults them to something that depends on where the code is located in your editor! Do never let Excel determine which sheet you mean. Make sure you define which sheet Excel has to use.
The defaults can be very tricky. Once you copy the code to another place the default may change without you noticing it and the code is working with the wrong sheet then.

Upvotes: 0

Related Questions