Hi,

Filter the column on non blanks, copy and then paste the data

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Elton Law" wrote in message
...
OOOh I said wrongly.
It should be

A1
Elton
A2
Kammi
A3 (blank)
A4 (blank)
A5
27-year
A6
Room 33
A7 (blank)
A8
Holiday

How I can write a function so that ... say cell B1 to B9 ... etc
Display as
Elton
Kammi
27-year
Room 33
Holiday

It skips all the blank row and display continuously in consecutive rows.
I cannot use filter and delete the blank row as this will affect other
rows
...
So wanna use function command to solve first. Thanks for help




"Elton Law" wrote:

Dear expert,

I have excel file and sor the fields selectively depending on the
criteria ...
Anyway, the outcome will be like this.

A1
Elton
B1
Kammi
C1 (blank)
D1 (blank)
E1
27-year
F1
Room 33
G1 (blank)
H1
Holiday

How I can write a function so that ... say cell C1 to C9 ... etc
Display as
Elton
Kammi
27-year
Room 33
Holiday

It skips all the blank row and display continuously in consecutive rows.
I cannot use filter and delete the blank row as this will affect other
rows
...
So wanna use function command to solve first. Thanks for help