find the last used column and paste the rows values alongside the last column.

[nihal90]

I am trying to format my worksheet. I am trying write a macro that finds the last used column (in this case it is Column V) and select the rows range for each company(Column E to Column J) and paste to the Column W( next to the last used column). And finally delete the empty rows. What I need is a row for each company. It would be nice if someone could help me on this.

 

[venkat1926]

too vague. pose a very small extract of data AGAIN EXPLAIN with reference to this data

 

[sijpie]

nihal
the following macro will copy the headers in E1:J1 and transpose them to the column after the last.

I don't know what you mean with 'delete the empty rows'


<font face=Courier New>Option Explicit<br><br><SPAN style="color:#00007F">Sub</SPAN> TransposeCompanies()<br><SPAN style="color:#007F00">' macro to transpose the headers in range E1:J1 to after _<br>  the last column in use.</SPAN><br>  <br>    <SPAN style="color:#00007F">Dim</SPAN> rTo <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> vCompHdr<br>    <SPAN style="color:#00007F">Const</SPAN> sHeaders = "E1:J1" <SPAN style="color:#007F00">' easy if you want to change this in future</SPAN><br>    <br>    <SPAN style="color:#007F00">' find last column in use</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rTo = Cells.Find("*", after:=Cells(1, Columns.Count), SearchOrder:=xlByColumns, searchdirection:=xlPrevious)<br>    <SPAN style="color:#007F00">' error check in case used on wrong sheet and sheet is empty</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> rTo <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Sheet is empty", vbCritical<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#007F00">' now transpose the headers of rangre E1:J1 to the column after the last in use</SPAN><br>    <SPAN style="color:#007F00">' load the headers in a variant array</SPAN><br>    vCompHdr = Range(sHeaders).Value<br>    <SPAN style="color:#007F00">' and put this transposed in next column</SPAN><br>    <SPAN style="color:#007F00">' ensure sufficient cells are allocated, in case your input range changes size in the future</SPAN><br>    Cells(1, rTo.Column + 1).Resize(UBound(vCompHdr, 2), 1).Value = Application.WorksheetFunction.Transpose(vCompHdr)<br>    <br>    <SPAN style="color:#007F00">'clean up</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rTo = <SPAN style="color:#00007F">Nothing</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>