repeat a cell in some other cells

Question

I have an excell fill with these data in it :

Name1
1
2
3
Name2
1
2
3
4
Name3
1
2
......

I want to have Names in a cell in front of each row below it like this :

1          Name1
2          Name1
3          Name1
1          Name2
2          Name2
3          Name2
4          Name2
1          Name3
2          Name3
....

is there any way to do this?

Answer 1

Here is one way you could approach this problem; it makes some assumptions that you may have to address if they turn out to be false.

Dim n As String
Dim v As String

Dim row As Long
Dim col As Integer

Dim c As Range

Dim destRow As Long
Dim destCol As Integer

'Assume your data is in column A, starting in row 1
row = 1
col = 1

'and that you want to drop it into columns C and D starting in row 1
destRow = 1
destCol = 3

Do
    Set c = ActiveSheet.Cells(row, col)
    If Left(c.Value, 4) = "Name" Then
        n = c.Value
    ElseIf n <> "" Then
        Cells(destRow, destCol).Value = c.Value
        Cells(destRow, destCol + 1).Value = n
        destRow = destRow + 1
    End If
    row = row + 1
Loop Until ActiveSheet.Cells(row, col).Value = ""