Autofill AAAA-AAAB etc

Peter621

New Member
Joined
Aug 6, 2019
Messages
5
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
Joined
Dec 3, 2007
Messages
965
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
Joined
Jan 9, 2008
Messages
14,841
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
Joined
Dec 3, 2018
Messages
8,795
Office Version
2007
Platform
Windows
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
Joined
Jun 12, 2014
Messages
32,185
Office Version
365
Platform
Windows
How about
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
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
UDF?

A​
B​
1​
AAAAA1: =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
Joined
Aug 6, 2019
Messages
5
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
Joined
Aug 6, 2019
Messages
5
Hi to you all, and thank you for your reply.
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
Joined
Dec 3, 2018
Messages
8,795
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,081,932
Messages
5,362,214
Members
400,672
Latest member
ExcelGrasshopper

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top