| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chapters???
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapters???
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapters???
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapters???
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapters???
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? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapters???
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Chapters???
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? |
Linear Mode
