It assumes A1 is "Chapter", B1 is "Name" and C1 is "Age".

It also assumes A2 is where the data starts.

Have you got spaces in cells A3, A4 etc so the cell isn't actually blank?

"Pluggie" wrote:

This code deletes all my content and leaves only the headers.
Somewhere there's a mistake...

"Sam Wilson" wrote:

Sub test()

Dim ws As Worksheet
Set ws = ActiveSheet

Dim i As Integer
Dim j As Integer
j = 1

Dim chap As String

With ws.Range("a1")

For i = 1 To ws.Cells.SpecialCells(xlCellTypeLastCell).Row
If IsEmpty(.Offset(j, 0)) Then
.Offset(j, 0).Value = chap
j = j + 1
Else
chap = .Offset(j, 0).Value
.Offset(j, 0).EntireRow.Delete
End If

Next i

End With

End Sub

"Pluggie" wrote:

Hello,

I have a list with names (B):

The layout is as follows:
chapters (column A), names (B), age (C).

The names (B) and corresponding ages (C) are devided into chapters.

Example:
Chapter Name Age
Family
Pete 25
Gill 23
Janet 44
Friends
Gemma 32
Vincent 33
Eric 32
Vicky 30
Lennart 35
John 33

Now...
I want a macro that copy-pastes the chapternames in front of each name in
that chapter and to delete the lines this data came from.
So the output will look like this:

Chapter Name Age
Family Pete 25
Family Gill 23
Family Janet 44
Friends Gemma 32
Friends Vincent 33
Friends Eric 32
Friends Vicky 30
Friends Lennart 35
Friends John 33

There are more chapters and all groups in these chapters vary in length.

Who can help me?