Try changing the button-click macro to:
Private Sub CommandButton1_Click()
Dim R, C, RepRow, MaxRow, MaxCol
Sheets("Sheet2").Range("A2:C65000").ClearContents
Sheets("Sheet1").Select
Sheets("Sheet1").Range("A1").Select
MaxRow = ActiveCell.SpecialCells(xlLastCell).Row
MaxCol = ActiveCell.SpecialCells(xlLastCell).Column
RepRow = 1
For R = 2 To MaxRow
For C = 2 To MaxCol
If (Sheets("Sheet1").Cells(R, C).Value <> "") Then
RepRow = RepRow + 1
Sheets("Sheet2").Cells(RepRow, 1).Value = _
Sheets("Sheet1").Cells(R, 1).Value
Sheets("Sheet2").Cells(RepRow, 2).Value = _
Sheets("Sheet1").Cells(1, C).Value
Sheets("Sheet2").Cells(RepRow, 3).Value = _
Sheets("Sheet1").Cells(R, C).Value
End If
Next C
Next R
Sheets("sheet2").Select
End Sub
Paul
>
>From: Dave Bonallack <davebonall...@hotmail.com>
>To: "excel-macros@googlegroups.com" <excel-macros@googlegroups.com>
>Sent: Mon, September 20, 2010 6:45:08 AM
>Subject: RE: $$Excel-Macros$$ Column Data in Rows Table
>
>Hi,
>Sorry, but someone else will have to take this one.
>Regards - Dave.
>
>________________________________
From: jai.ca...@gmail.com
>To: excel-macros@googlegroups.com
>Subject: $$Excel-Macros$$ Column Data in Rows Table
>Date: Sun, 19 Sep 2010 00:43:18 +0530
>
>
>Hi Dave,
>
>
>I also have the similar issue.
>
>Name A B C D E
>AA 10 14 11 13 14
>AB 13 13 14 14 12
>AC 13 10 14 11 14
>AD 14 10 10 13 13
>
>
>
>The solution you provided is
>
>AA 10
>AA 14
>AA 11
>AA 13
>AA 14
>AB 13
>AB 13
>AB 14
>AB 14
>AB 12
>AC 13
>AC 10
>AC 14
>AC 11
>AC 14
>AD 14
>AD 10
>AD 10
>AD 13
>AD 13
>
>but I need this:-
>
>AA A 10
>AA B 14
>AA C 11
>AA D 13
>AA E 14
>AB A 13
>AB B 13
>AB C 14
>AB D 14
>AB E 12
>AC A 13
>AC B 10
>AC C 14
>AC D 11
>AC E 14
>AD A 14
>AD B 10
>AD C 10
>AD D 13
>AD E 13
>
>
>
>
>
>
>----- Original Message -----
>From: Dave Bonallack
>To: excel-macros@googlegroups.com
>Sent: Wednesday, September 08, 2010 2:32 PM
>Subject: RE: $$Excel-Macros$$ Column Data in Rows Table
>
>Hi Sayyad,
>Have a look at the attached.
>Click the button on sheet 2.
>Regards - Dave
>
>> Date: Tue, 7 Sep 2010 23:30:20 -0700
>> Subject: $$Excel-Macros$$ Column Data in Rows Table
>> From: anamika2...@gmail.com
>> To: excel-macros@googlegroups.com
>>
>> Hi ,
>>
>> I have a case where I get the data in following format
>>
>> Name A B C D E
>> AA 10 14 11 13 14
>> AB 13 13 14 14 12
>> AC 13 10 14 11 14
>> AD 14 10 10 13 13
>>
>> I need to convert the above format to the data in following format.
>> The no columns & no of rows in the data vary, can you help me to get
>> this done
>>
>> AA A 10
>> AA B 14
>> AA C 11
>> AA D 13
>> AA E 14
>> AB A 13
>> AB B 13
>> AB C 14
>> AB D 14
>> AB E 12
>> AC A 13
>> AC B 10
>> AC C 14
>> AC D 11
>> AC E 14
>> AD A 14
>> AD B 10
>> AD C 10
>> AD D 13
>> AD E 13
>>
>> --
>>----------------------------------------------------------------------------------
>>
>>-
>> Some important links for excel users:
>> 1. Follow us on TWITTER for tips tricks and links :
>>http://twitter.com/exceldailytip
>> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>> 3. Excel tutorials at http://www.excel-macros.blogspot.com
>> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
>> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>>
>> To post to this group, send email to excel-macros@googlegroups.com
>>
>> <><><><><><><><><><><><><><><><><><><><><><>
>> HELP US GROW !!
>>
>> We reach over 7000 subscribers worldwide and receive many nice notes about
>> the
>
>>learning and support from the group.Let friends and co-workers know they can
>>subscribe to group at http://groups.google.com/group/excel-macros/subscribe
>
>--
>----------------------------------------------------------------------------------
>
>
>Some important links for excel users:
>1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>3. Excel tutorials at http://www.excel-macros.blogspot.com
>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>To post to this group, send email to excel-macros@googlegroups.com
>
><><><><><><><><><><><><><><><><><><><><><><>
>HELP US GROW !!
>
>We reach over 7000 subscribers worldwide and receive many nice notes about the
>learning and support from the group.Let friends and co-workers know they can
>subscribe to group at http://groups.google.com/group/excel-macros/subscribe
>
>--
>----------------------------------------------------------------------------------
>
>
>Some important links for excel users:
>1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>3. Excel tutorials at http://www.excel-macros.blogspot.com
>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>To post to this group, send email to excel-macros@googlegroups.com
>
><><><><><><><><><><><><><><><><><><><><><><>
>HELP US GROW !!
>
>We reach over 7000 subscribers worldwide and receive many nice notes about the
>learning and support from the group.Let friends and co-workers know they can
>subscribe to group at http://groups.google.com/group/excel-macros/subscribe
>--
>----------------------------------------------------------------------------------
>
>
>Some important links for excel users:
>1. Follow us on TWITTER for tips tricks and links :
>http://twitter.com/exceldailytip
>2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
>3. Excel tutorials at http://www.excel-macros.blogspot.com
>4. Learn VBA Macros at http://www.quickvba.blogspot.com
>5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>To post to this group, send email to excel-macros@googlegroups.com
>
><><><><><><><><><><><><><><><><><><><><><><>
>HELP US GROW !!
>
>We reach over 7000 subscribers worldwide and receive many nice notes about the
>learning and support from the group.Let friends and co-workers know they can
>subscribe to group at http://groups.google.com/group/excel-macros/subscribe
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 7000 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
