Autofill numbers in dynamic range

ichbinich

New Member
Joined
Jan 2, 2018
Messages
12
Hi all
I like to auto fill numbers
i have a macro like this and works fine
VBA Code:
 Range("A1") = "Line"
    Range("A2") = "1"
    Range("A3") = "2"
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A2:A3").autofill Destination:=Range("A2:A" & LastRow)
But the problem happen some time i have only 1 line so it need to enter only 1 not the 2 and not autofill until the end
any advise
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
How about:
VBA Code:
Sub Test()
Dim LastRow As Long, i As Long, count as Long
Range("A1")= "Line"
count = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).row
For i = 2 To LastRow
    Range("A" & i).Value = count
    count = count + 1
Next i
End Sub
 
Upvote 0
Solution
An alternative
VBA Code:
Sub AutoNumberFill()
    Dim x As Long
    Dim rn As Long
    Dim cn As String, sn As Long
    cn = InputBox("Which Column to fill?")
    rn = InputBox("How many rows to fill?")
    sn = InputBox("Which row to start fill?")

    Application.ScreenUpdating = False
    Application.StatusBar = "Macro Running"
    x = InputBox("What is the starting number?")
    Range(cn & sn).Select
    Do Until ActiveCell.Row = rn + sn
        If ActiveCell.EntireRow.Hidden = False Then
            ActiveCell.Value = x
            x = x + 1
        End If
        ActiveCell.Offset(1).Select
    Loop
    Application.ScreenUpdating = True
    Application.StatusBar = "Completed"
End Sub
 
Upvote 0
I have no idea how LastRow is determine in your program. Looks like the sub is for column A only. @alansidman solution is more comprehensive and flexible. It can be applied anywhere on the sheet

Here I'm assuming that you type any character on the last row and run the code.
VBA Code:
Sub Test()

Range("A1") = "Line"
Range("A2") = 1
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rngSet = Range("A2", "A" & LastRow)
Range("A2").Select
If rngSet.Count = 1 Then
    rngSet = 1
Else
    Selection.AutoFill Destination:=rngSet, Type:=xlFillSeries
End If

End Sub
 
Upvote 0
The combo boxes give you flexibility to pick how much and where. Did you give it a try?
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top