How to programatically transpose Rows into Columns

**naeemdotcom** · 01-09-2011, 06:47 AM

Hi All,

Here is a sample sheet. I am interested in doing following steps by VBA code.

1. Delete all ROWS in which Column AB is empty.
2. Select and Copy Column AB of all ROWS in which Column AB has text starting string "R000-" or "R020-" or "R040-" or .... upto ""R580-".
3. PASTE these ROWS in separate sheet.
4. Delete duplicate ROWS.
5. Delete the first 5 characters (headers) of all these rows.
6. Convert the Text-to-column conversion of the text at fixed width of length 3.
7. This will give us 20 columns, since the length of the text is 60 numeric characters.
8. Transpose these into 1 column. Ultimately the column will have 600 entries.

Any help would be highly appreciated.

**naeemdotcom** · 01-09-2011, 07:04 AM

Here is the sample-done file.

In sheet 2 the selected data from Column AB is pasted after removing empty and duplicate rows. The data is also converted from Text-to-Column at fixed width of length 3, as explained the steps of the original post.

In sheet 3 the ROWS are transposed into a single column. Just the first 4 rows are done just to show the result.

So in a nutshell i need to get sheet 3 from sheet 1 using VBA code.

Any help would be greatly appreciated.

**Marcol** · 01-09-2011, 07:20 AM

Try recording a macro and take it from there?

This is your third thread, all of which can be solved without macros.
The previous two are apparently solved, but you haven't marked then as so.

This forum is intended to help people to learn Excel and the techniques involved.
It is not a free pool of agency workers that do your work for you.