Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

transpose range of cells in excel 2007

Dear experts
I know how to trasnpose a column in a row and vice versa, what I want to do in this case, without using vb script if possible is to take a column exported from my property database which simply has a gap (empty cell) between each record and move that as a whole chunk across as a row. This is because the mail merge merge program in Word (2007) only seems to import the field headers as a row, unless someone can tell me how to mail merge from that layout instead it would acheive the same thing.
Fro example, I have 3 records all on column A, the first 2 contain 4 rows and the last one is 5 rows, how would I get this trasnposed into 3 columns using the empty cell between each reacord as a delimiter, or I could insert a delimiter character in between each record such as a tab or comma if that would make it easier.
Your help is much appreciated.
0
revahi
Asked:
revahi
1 Solution
 
revahiAuthor Commented:
Apologies for my spelling, I rushed this Q out in a hurry without previewing first! I trust it's still understood otherwise please query and I will correct.
thank you
0
 
StephenJRCommented:
Could you post a small sample workbook?
0
 
Arno KosterCommented:
I am not sure if it could be done without using VB, but with a macro it is fairly easy, assuming that you start with an empty worksheet and copy the records to column A
Sub process()

    target_row = 1
    target_col = 3
    For Row = 1 To UsedRange.Rows.Count

        If Range("A" & Row) = "" Then
            '-- empty cell = delimiter, move to new column
            target_row = 1
            target_col = target_col + 1
        Else
            target_row = target_row + 1
            Cells(target_row, target_col) = Range("A" & Row)
        End If

    Next Row

End Sub

Open in new window



0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now