Autofill Numbers in Sequence, increments of 30 over multiple columns

[Plukey]

I have a few VBA's that accomplish the task on a single column. But what Id like to do, is enter a start number and consecutively autofill 270 times across 9 columns in increments of 30.
These Serial Numbers get used quickly, If I could run a Vba that ask for a start # and autofill it would save some time.

Ex.
1 6 11
2 7 12
3 8 13
4 9 14
5 10 15
etc...….

Sub AddSerialNum1()
Dim i As Integer
On Error GoTo Last
i = InputBox("Enter Value", "Enter Serial Numbers")
For i = 1 To i
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Activate
Next i
Last:
Exit Sub
End Sub

or

Sub AddSerialNum2()
Dim i As Long, J As Long
Application.ScreenUpdating = 0

i = Application.InputBox(Prompt:="Start", Type:=1)
J = Application.InputBox(Prompt:="end", Type:=1)
With Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1)
        .Value = i
        .AutoFill .Resize(J, 1), xlFillSeries
    End With
    Application.ScreenUpdating = 1


End Sub

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Plukey]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Apologies Thank you, I updated my profile with the details. I'm using 2016 Windows

 

[Fluff]

Thanks for that, how about

Sub Plukey()
   Dim r As Long, c As Long
   Dim Ary As Variant, Start As Variant
   
   ReDim Ary(1 To 30, 1 To 9)
   
   Start = InputBox("Please enter start number")
   If Start = "" Then Exit Sub
   For c = 1 To UBound(Ary, 2)
      For r = 1 To UBound(Ary)
         Ary(r, c) = Start
         Start = Start + 1
      Next r
   Next c
   Range("A2").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

 

[Plukey]

Thanks for that, how about

Sub Plukey()
   Dim r As Long, c As Long
   Dim Ary As Variant, Start As Variant
  
   ReDim Ary(1 To 30, 1 To 9)
  
   Start = InputBox("Please enter start number")
   If Start = "" Then Exit Sub
   For c = 1 To UBound(Ary, 2)
      For r = 1 To UBound(Ary)
         Ary(r, c) = Start
         Start = Start + 1
      Next r
   Next c
   Range("A2").Resize(UBound(Ary), UBound(Ary, 2)).Value = Ary
End Sub

Absolutely perfect!!! thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Rick Rothstein]

You could also write the macro this way (no loops)...

Sub Plukey()
  Dim StartNumber As Variant
  StartNumber = InputBox("Please enter start number")
  If StartNumber Like "#*" Then
    With Range("A2").Resize(30, 9)
      .Formula = "=" & StartNumber & "+ROW(A1)-1+30*(COLUMN(A1)-1)"
      .Value = .Value
    End With
  End If
End Sub

 

[KalilMe]

@Rick Rothstein what if the same sequence I mean should be 1 to 30 for all columns

 

[Rick Rothstein]

Not sure what you mean by "all columns" (I'll assume 9 as was stated in the original posting)...

Sub OneToThirtyAllColumns()
  Range("A1:I30").Value = [ROW(1:30)]
End Sub

 

[KalilMe]

that's great ! but what I if choose specific columns (A,C,F,L,M) how should be ?