replication

[weidlh46]

Hi,

I have thousands of rows, just one column.

Basically I want to transform from
A
B
C


to

equivalent rows of each cell; e.g.
A
A
A
B
B
B
C
C
C

Do I need to write Macro to accomplish that? Sorry, I am new to Macro.3

Thanks so much.

Wei

[Bob Phillips]

Public Sub AddRows()
Dim lastrow As Long
Dim i As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = lastrow To 5 Step -1
        
            .Rows(i + 1).Resize(2).Insert
            .Cells(i, "B").Resize(3).Value = .Cells(i, "B").Value
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

[weidlh46]

Thanks so much, Bob. It works.

Public Sub AddRows()
Dim lastrow As Long
Dim i As Long

    Application.ScreenUpdating = False
    
    With ActiveSheet
    
        lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
        For i = lastrow To 5 Step -1
        
            .Rows(i + 1).Resize(2).Insert
            .Cells(i, "B").Resize(3).Value = .Cells(i, "B").Value
        Next i
    End With

    Application.ScreenUpdating = True
End Sub

[FDibbins]

Your use of letters is probably just an example, but this will also do it for you without VBA.

Enter A in A3, then use this, copied down...
=IF(MOD(ROW(),3)<>0,AG3,CHAR(CODE(A3)+1))

[weidlh46]

Thanks very much. It also works.

Your use of letters is probably just an example, but this will also do it for you without VBA.

Enter A in A3, then use this, copied down...
=IF(MOD(ROW(),3)<>0,AG3,CHAR(CODE(A3)+1))

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

[weidlh46]

Hi FDibbins, I have marked it as solved and clicked on the star. Thanks again

[FDibbins]

Thank you, always appreciated