Autofill AAAA-AAAB etc

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,787
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:

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
30,247
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
7,759
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
 

Forum statistics

Threads
1,077,850
Messages
5,336,740
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top