Organize Rows and Columns

  • Thread starter Thread starter Hady
  • Start date Start date
H

Hady

Hi!


This is an example of what I have now

GENDER COUNTRY RELIGION
Male Canada Christian

FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH ...
John Smith Peter 1/1/1960
Johnny Smithson Pete 1/1/1950
John Smith Peter 1/1/1960
Johnny Smithson Pete 1/1/1950


GENDER COUNTRY RELIGION
Female Canada Christian

FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH ...
Cindy Jack Antoinette 1/1/1960
Christine Smithson Pete 1/1/1950


GENDER COUNTRY RELIGION
Female France Christian

FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH ...
Marie Smith Antoinette 1/1/1960
Isabelle Smithson Pete 1/1/1950



What I want to have is

FIRST_NAME LAST_NAME MIDDLE_NAME DATE_OF_BIRTH GENDER COUNTRY
RELIGION
John Smith Peter 1/1/1960 Male Canada
Christian
Johnny Smithson Pete 1/1/1950 Male Canada
Christian
John Smith Peter 1/1/1960 Male Canada
Christian
Johnny Smithson Pete 1/1/1950 Male Canada
Christian
Cindy Jack Antoinette 1/1/1960 Female Canada
Christian
Christine Smithson Pete 1/1/1950 Female Canada
Christian
Marie Smith Antoinette 1/1/1960 Female France
Christian
Isabelle Smithson Pete 1/1/1950 Female France
Christian



I would like to find a way to convert from the previous to this new
form (all columns next to each other),
I cannot Copy/Paste all rows because I have sooo many rows so I need
an automated way!
I appreciate any suggestions...


Many thanks in advance!
 
Hady,

Try this

Option Explicit

Sub Test()
Dim iLastRow As Long
Dim i As Long
Dim iRow As Long
Dim sSex As String
Dim sCountry As String
Dim sReligion As String
Dim fProcess As Boolean
Dim rngDel As Range

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rngDel = Cells(1, "A")
For i = 1 To iLastRow
If Cells(i, "A").Value = "" Then
Set rngDel = Union(rngDel, Cells(i, "A"))
fProcess = False
ElseIf Cells(i, "A").Value = "GENDER" Then
sSex = Cells(i + 1, "A").Value
sCountry = Cells(i + 1, "B").Value
sReligion = Cells(i + 1, "C").Value
Set rngDel = Union(rngDel, Cells(i, "A"))
Set rngDel = Union(rngDel, Cells(i + 1, "A"))
i = i + 1
fProcess = False
ElseIf Cells(i, "A").Value = "FIRST_NAME" Then
Set rngDel = Union(rngDel, Cells(i, "A"))
fProcess = True
Else
Cells(i, "E").Value = sSex
Cells(i, "F").Value = sCountry
Cells(i, "G").Value = sReligion
End If
Next i

If Not rngDel Is Nothing Then
rngDel.EntireRow.Delete
End If

Rows(1).Insert
Range("A1:G1").Value = Array("FIRST_NAME", "LAST_NAME", _
"MIDDLE_NAME", "DATE_OF_BIRTH", _
"GENDER", "COUNTRY", _
"RELIGION")

End Sub
 
Back
Top