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?

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?
>

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?
> >

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?
> > >

Try the below macro with your data in Cols A.B.C

Sub MyMacro()
Dim lngRow As Long, strChapter As String
lngRow = 2
Do While Trim(Range("A" & lngRow)) & _
Trim(Range("B" & lngRow)) & Trim(Range("C" & lngRow)) <> ""

If Trim(Range("A" & lngRow)) <> "" Then _
strChapter = Trim(Range("A" & lngRow))

If Trim(Range("B" & lngRow)) = "" Then _
Rows(lngRow).Delete

If Trim(Range("A" & lngRow)) = "" Then _
Range("A" & lngRow) = strChapter
lngRow = lngRow + 1
Loop
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"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?
>

Hi Jacob!

We are almost there with your code.
I first pasted it in and ran the macro. It worked partially.
However... all data in column A was deleted.

Then I manually deleted the contents of the "empty" cells in column A, ran
the macro again and it worked like a charm.

So somehow the trim you put in doesn't really trim the empty cells in A.
Any ideas?

"Jacob Skaria" wrote:

> Try the below macro with your data in Cols A.B.C
>
> Sub MyMacro()
> Dim lngRow As Long, strChapter As String
> lngRow = 2
> Do While Trim(Range("A" & lngRow)) & _
> Trim(Range("B" & lngRow)) & Trim(Range("C" & lngRow)) <> ""
>
> If Trim(Range("A" & lngRow)) <> "" Then _
> strChapter = Trim(Range("A" & lngRow))
>
> If Trim(Range("B" & lngRow)) = "" Then _
> Rows(lngRow).Delete
>
> If Trim(Range("A" & lngRow)) = "" Then _
> Range("A" & lngRow) = strChapter
> lngRow = lngRow + 1
> Loop
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "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?
> >

And did you find a solution?

"Jacob Skaria" wrote:

> Try the below macro with your data in Cols A.B.C
>
> Sub MyMacro()
> Dim lngRow As Long, strChapter As String
> lngRow = 2
> Do While Trim(Range("A" & lngRow)) & _
> Trim(Range("B" & lngRow)) & Trim(Range("C" & lngRow)) <> ""
>
> If Trim(Range("A" & lngRow)) <> "" Then _
> strChapter = Trim(Range("A" & lngRow))
>
> If Trim(Range("B" & lngRow)) = "" Then _
> Rows(lngRow).Delete
>
> If Trim(Range("A" & lngRow)) = "" Then _
> Range("A" & lngRow) = strChapter
> lngRow = lngRow + 1
> Loop
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "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?
> >