Chapters???

  • Thread starter Thread starter Pluggie
  • Start date Start date
P

Pluggie

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
 
This code deletes all my content and leaves only the headers.
Somewhere there's a mistake...
 
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?
 
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
 
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?
 
And did you find a solution?

Jacob Skaria said:
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
Click to expand...
 
Back
Top