Excel for alphabet continuous?

Question

hi how to do where i type

a

b

c

then i highlight and drag, it will automatically update the next row to d,e,f,g, until HA,HB?

Answer 1

To get a series of rows to show the letters you indicated without entering all of them, you will have to enter a formula in one of the cells and copy it to all of the cells that should contain the letters

The formula would be =Letters(Cell, Adjustment)  where Cell is a reference to a cell in the row that you want to determine the letter(s) shown in the cell with the formula.

For the formula to work the following public function must exist in a module added to your VBAProject

Public Function Letters(Target As Range, Adjustment As Long) As String

Dim sRL, sLL As String

If Target.Row + Adjustment > 0 And Target.Row + Adjustment < 703 Then

    sRL = Mid$("ZABCDEFGHIJKLMNOPQRSTUVWXY", (Target.Row + Adjustment) Mod 26 + 1, 1)

    If Target.Row + Adjustment > 26 Then

        sLL = Mid$("ZABCDEFGHIJKLMNOPQRSTUVWXY", (Int((Target.Row + Adjustment - 1) / 26)) Mod 26 + 1, 1)

    Else

        sLL = ""

    End If

Else

    sRL = "": sLL = ""

End If

Letters = sLL + sRL

End Function

This is one place where a cell can reference itself in a formula and Excel doesn’t mind so if you want column A to list the Excel column names down the rows, you just need to put the formula =Letters(A1,0) in cell A1 and copy it down through line 702 to produce values A through ZZ (I used uppercase where your example used lower case – it is easy to switch but be sure to notice that the alphabets start out ZAB not ABC.)

If you want the list to start in cell A1 with AA you would use =Letters(A1, 26)

If you type =Letters(A1,0) into cell A25 the list will start there with A.

If you don’t know how to put the code into you VBAProject and want to, modify your question to indicate that and I will add instructions to my answer.