Solved: Skip blank cells

[YasserKhalil]

Hi guys
Here's an attachment with my request

[Bob Phillips]

Is that two ways the data might be given and the single way it should be returned, or is it two lines to be merged?

[YasserKhalil]

Hi Mr. xld
How are you today?
-------------------
The answer:
the single way it should be returned
The result should be without blank cells ...

[brettdj]

Its a quick kill using SpecialCells. The non VBA equivalent is to
select your data
press F5
select ... 'Special' and check 'Blanks"
delete the resultThe code below uses an error handler to suppress any errors if there are no blank cells
Sub MethodA()
On Error Resume Next
Rows(1).SpecialCells(xlBlanks).Delete xlToLeft
End Sub

Cheers

Dave

[YasserKhalil]

Thank you Mr. Dave
It works very good
Thanks for help

[Bob Phillips]

So what was the row with the zeroes all about?

[YasserKhalil]

I tried to skip blank cells by using formulas at first
Is there a way to do so by formulas??

[Bob Phillips]

Yes, you can

=IF(ISERROR(SMALL(IF($A$1:$H$1<>"",COLUMN($A$1:$H$1),""),COLUMN(A$1))),"",
INDEX($A$1:$H$1,SMALL(IF($A$1:$H$1<>"",COLUMN($A$1:$H$1),""),COLUMN(A$1))))

and copy across.

This is an array formula.
This means that you use Ctrl-Shift-Enter to commit the formula, not just Enter (array Enter it). Excel will put curly brackets around the formula in the formula bar, you don#t do this.
If you need to change the formula at any time, you must array Enter it again.

[YasserKhalil]

Thanks Mr. xld
That's perfect solution..