macro for deleting blank rows and rows with text

[cmefly]

hi,

i've got a column containing cells with just numbers, text, or blank.
I want to get rid of all the text and blank rows - and then transpose the column into the first row.....

any suggestions?

 

[kskinne]

select your column, press F5 to open the GoTo window

press 'special'

select the 'constants' radio button, and then uncheck everything except for 'text'. then press 'ok'. this will highlight all the cells in the column containing text. delete the rows.

then go back to the goto, special window, this time selecting the 'blanks' radio button to select and delete the blank cells.

now all that remains should be your number cells.

higlight the entire range of data in your column and copy it to the clipboard. then click in the place where you want to paste to, and right-click, then select 'paste special'. check the 'transpose' button, and press 'ok'.


hth
kevin

 

[cmefly]

hi keith,

it works perfectly except for the transpose part. I've got 449 rows to transpose into columns. excel only holds upto 255 columns...right?...this won't be possible..?

 

[kskinne]

excel has 256 columns max, so you're correct you won't be able to transpose your entire column into one row.

however, you could highlight the first 256 rows in your column and transpose these into the first row, then highlight the remaining and transpose these into the next row. it depends on what you need to do this for, and how your data needs to be laid out

hth

Kevin

 

[cmefly]

thanks keith......i think i'll just keep it the way it is and not transpose it at the moment