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

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

Give this a try, just set the Source and DestinationStartCell variable to
the range and cell that you want...

Sub TransposeWithoutBlanks()
Dim Index As Long, OffsetAmount As Long
Dim Source As Range, DestinationStartCell As Range
Set Source = Range("A1:H1")
Set DestinationStartCell = Range("B2")
For Index = 1 To Source.Count
If Source(Index).Value < "" Then
DestinationStartCell.Offset(OffsetAmount).Value = Source(Index).Value
OffsetAmount = OffsetAmount + 1
End If
Next
End Sub

NOTE: You can't overwrite your actual data with this method, so I used B2
instead of the B1 that you asked for as the start cell for the output list.
If you really do want to overwrite the original data, you can just move the
output range into the correct location at the end of the above code.

--
Rick (MVP - Excel)


"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

Sorting would group those items together, but maybe you don't want them in
alpha-order. Try this:
=IF(ROWS($1:1)<=COUNTA(A1:A20),INDEX(A1:A20,SMALL( IF(A1:A20<"",ROW(A1:A20)-MIN(ROW(A1:A20))+1),ROWS($1:1))),"")\\Enter
with Ctrl + Shift + Enter, not just Enter

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Elton Law" wrote:

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

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