Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #8994

Re: Excel: convert columns to rows

Show all headers | View raw

I'm not even an "advanced" Excel user, but isn't that what Pivot Tables are 
for? For a brief period (a few versions) Access had Pivot Table View, and 
has always had Crosstab Queries.  By the way, Access is the focus of this 
newsgroup, so you may find better answers in one devoted to Excel.

-- 
Larry Linson
  Microsoft Office Access MVP
  Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Ty" <tyrone_bak@yahoo.com> wrote in message 
news:c9e78038-c0b7-4e76-a090-1b0dbd8aa2bb@googlegroups.com...
Anyone,

Please help.  I have an excel Macro to convert columns to rows.  Unable to 
get it to work.  Place cursor in A1 on the sheet with the data.  Receive 
error.  Stops at "Set target"  Maybe I am using excel incorrectly.  I don't 
know.  My problem:  I need the data sheet moved to a new sheet with the 
columns converted to rows.  Column header will be listed for every item 
underneath that column header in one 1 row.  So, A123, A456...etc...etc. 
And some columns could have up to 300 rows of data.  And then some could 
have just 3 rows.  Here is the macro(solution):


'assuming your table may have text or number

Sub transposee()

Dim target As Worksheet
Dim lastrow As Long
Dim lastcol As Long
Dim i As Long, z As Long

    Set target = Worksheets("Data99") 'change as appropriate

    With ActiveSheet

        .Columns("W:W").ClearContents
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'lastcol = .Cells(1, .Columns.Count).End(xlLeft).Column
        lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        z = 2

        For i = 2 To lastcol

            target.Cells(z, "W").Resize(lastrow).Value = .Cells(1, i)
            .Cells(2, i).Resize(lastrow).Copy target.Cells(z, "X")

            z = z + lastrow
        Next
    End With
End Sub

Thanks,
Ty 

Back to comp.databases.ms-access | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Excel: convert columns to rows Ty <tyrone_bak@yahoo.com> - 2014-02-10 21:07 -0800
  Re: Excel: convert columns to rows "Access Developer" <accdevel@gmail.com> - 2014-02-10 23:31 -0600
  Re: Excel: convert columns to rows "David Hare-Scott" <secret@nospam.com> - 2014-02-11 22:02 +1100
    Re: Excel: convert columns to rows Ty <tyrone_bak@yahoo.com> - 2014-02-13 18:40 -0800

csiph-web