Autofill AAAA-AAAB etc

Peter621

New Member
Hi one and all. I'm new to Excel, so fairly inept with formula etc. I had done a search of questions asked, and found a question that nearly but not quite hit the point. The question posed by 'cmendes on May 18th 2011. What I'm trying to do is to populate cell A1 with AAAA, then autofill (vertically) with AAAB all the way to ZZZZ. Why, I here you ask, I'm trying to set up a spread sheet of all UK aircraft registrations from AAAA-ZZZZ.
MTIA
Peter

jkpieterse

Well-known Member
You do know that means about half a million rows? This can be done using PowerQuery. Which Excel version are you using?

MickG

MrExcel MVP
Try this for results in column "A":-
456976 Rows !!!!
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG06Aug23
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] b [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] d [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]For[/COLOR] a = 65 To 90
[COLOR="Navy"]For[/COLOR] b = 65 To 90
[COLOR="Navy"]For[/COLOR] c = 65 To 90
[COLOR="Navy"]For[/COLOR] d = 65 To 90
n = n + 1
Cells(n, 1) = Chr(a) & Chr(b) & Chr(c) & Chr(d)
[COLOR="Navy"]Next[/COLOR] d
[COLOR="Navy"]Next[/COLOR] c
[COLOR="Navy"]Next[/COLOR] b
[COLOR="Navy"]Next[/COLOR] a
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

DanteAmor

Well-known Member
Try this, I think it is faster.

Code:
``````Sub Autofill_AAAA_AAAB()
Dim i As Long, j As Long, k As Long, l As Long, n As Long
Dim a()
Application.ScreenUpdating = False
n = 0
Range("A:E").ClearContents
For i = 1 To 26
Range("B" & i & ":E" & i).Value = Evaluate("=SUBSTITUTE(ADDRESS(1," & i & ",4),""1"","""")")
Next
For i = 1 To 26
For j = 1 To 26
For k = 1 To 26
For l = 1 To 26
ReDim Preserve a(n)
a(n) = Cells(i, 2) & Cells(j, 3) & Cells(k, 4) & Cells(l, 5)
n = n + 1
Next
Next
Next
Range("A" & Rows.Count).End(xlUp)(2).Resize(n).Value = Application.Transpose(a)
n = 0
ReDim a(n)
Next
Range("B:E").ClearContents
MsgBox "End"
End Sub``````

Last edited:

Fluff

MrExcel MVP, Moderator
Code:
``````Sub Peter621()
Dim i As Long, j As Long, k As Long, l As Long, r As Long
Dim Ary As Variant

ReDim Ary(1 To 26 ^ 4, 1 To 1)
For i = 65 To 90
For j = 65 To 90
For k = 65 To 90
For l = 65 To 90
r = r + 1
Ary(r, 1) = Chr(i) & Chr(j) & Chr(k) & Chr(l)
Next l
Next k
Next j
Next i
Range("A1").Resize(r).Value = Ary
End Sub``````
Beaten 2it
Very similar to MickG' method

Last edited:

shg

MrExcel MVP
UDF?

 A​ B​ 1​ AAAA A1: =ColLtr(ROW() + 18278) 2​ AAAB 3​ AAAC 4​ AAAD 5​ AAAE 6​ AAAF 7​ AAAG 8​ AAAH 9​ AAAI 10​ AAAJ

Code:
``````Function ColLtr(ByVal iCol As Long) As String
' shg 2012
' Good for any positive Long
If iCol > 0 Then ColLtr = ColLtr((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
End Function``````

Peter621

New Member
Hi jkpieterse, thanks for your reply. Yes, I know there will be one or two rows, but then there are one or two aircraft on the British register. Excel 2013 is the version on my laptop.
Regards
Peter

Peter621

New Member
I see that your answers are a 'code' solution. Umm, so what do I do with it, so to speak?
Regards
Peter

DanteAmor

Well-known Member
HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Autofill_AAAA_AAAB) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.