View Full Version : Solved: Skip blank cells
YasserKhalil
07-03-2010, 01:46 PM
Hi guys
Here's an attachment with my request
Bob Phillips
07-03-2010, 02:05 PM
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
07-03-2010, 02:33 PM
Hi Mr. xld
How are you today?
-------------------
The answer:
the single way it should be returned
The result should be without blank cells ...
brettdj
07-03-2010, 09:28 PM
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
07-04-2010, 01:04 AM
Thank you Mr. Dave
It works very good
Thanks for help
Bob Phillips
07-04-2010, 02:14 AM
So what was the row with the zeroes all about?
YasserKhalil
07-04-2010, 03:04 AM
I tried to skip blank cells by using formulas at first
Is there a way to do so by formulas??
Bob Phillips
07-04-2010, 05:38 AM
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
07-04-2010, 12:15 PM
Thanks Mr. xld
That's perfect solution..
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.