Autofill AAAA-AAAB etc

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,910
If you just want a native formula, put this in A1 and copy it down to A456976:

=CHAR(65+INT((ROW()-1)/26^3))&CHAR(65+MOD(INT((ROW()-1)/26^2),26))&CHAR(65+MOD((ROW()-1)/26,26))&CHAR(65+MOD(ROW()-1,26))


Or a bit more succinctly if you have Excel 365:

=CONCAT(CHAR(65+MOD(INT((ROW()-1)/26^{3,2,1,0}),26)))
 
Last edited:

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Peter621

New Member
Joined
Aug 6, 2019
Messages
5
Hi to you all.
Sorry for delay in replying, I was away for the day. My smacked is gobbed! Brilliant, all working wonderfully, I owe you all a pint.
Now all I have to do is learn to understand code and formula, it's like hieroglyphics to me. Any literature or on-line tutorials you would recommend?
Would there be a simple way of putting a blank row at A1, I've tried 'insert row', but that then auto populates to 'AAAA', reason being, I'd like to put in column headers.
Thanks again.
Regards
Peter
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,630
Office Version
365
Platform
Windows
For my code simply make the change in red
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("[COLOR=#ff0000]A2[/COLOR]").Resize(r).Value = Ary
End Sub
To learn more about arrays have a look here
https://excelmacromastery.com/excel-vba-array/
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,173
Office Version
2007
Platform
Windows
Hi to you all.
Sorry for delay in replying, I was away for the day. My smacked is gobbed! Brilliant, all working wonderfully, I owe you all a pint.
Now all I have to do is learn to understand code and formula, it's like hieroglyphics to me. Any literature or on-line tutorials you would recommend?
Would there be a simple way of putting a blank row at A1, I've tried 'insert row', but that then auto populates to 'AAAA', reason being, I'd like to put in column headers.
Thanks again.
Regards
Peter
In fact my macro in post #4 starts in A2
 

Watch MrExcel Video

Forum statistics

Threads
1,102,316
Messages
5,486,113
Members
407,535
Latest member
Jens_14

This Week's Hot Topics

Top