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

Groups > comp.databases.ms-access > #8993 > unrolled thread

Excel: convert columns to rows

Back to article view | Back to comp.databases.ms-access

Contents

  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

#8993 — Excel: convert columns to rows

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

[toc] | [next] | [standalone]

#8994

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 

[toc] | [prev] | [next] | [standalone]

#8995

Ty wrote:
> Anyone,
> 
> Please help.  I have an excel Macro to convert columns to rows. 
> Unable to get it to work. 
> 

..snip....

Save yourself some trouble and look up 'transpose' in help.

D

[toc] | [prev] | [next] | [standalone]

#9014

On Tuesday, February 11, 2014 5:02:51 AM UTC-6, David Hare-Scott wrote:
> Ty wrote:
> 
> > Anyone,
> 
> > 
> 
> > Please help.  I have an excel Macro to convert columns to rows. 
> 
> > Unable to get it to work. 
> 
> > 
> 
> 
> 
> ..snip....
> 
> 
> 
> Save yourself some trouble and look up 'transpose' in help.
> 
> 
> 
> D

I did.  Transpose will only take the header in the cell 1 time.  Then the rows underneath that header will be on that same row.  I needed the header duplicated in cell 1 as many times as there is an item in the column.

I have the problem resolved.

Thanks,
Ty

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web