Removing blanks / sorting - common methods not applicable
 

I know several ways to remove blank cells, however none really works
here.

Autofilter doesn't work because I need a dynamic method

-=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))-
...doesn't work either because it doesn't consider the sorting.

In the attached file: The number 40658 has certain dates assigned to
it.
If aboves methode is being used, the dates are all messed up.
(Autofilter would do the trick , but its not dynamic)

How do I dynamically remove the blank cells/rows and keep the sorting
intact?


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4811 |
+-------------------------------------------------------------------+

--
Dark_Templar
------------------------------------------------------------------------
Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279
View this thread: http://www.excelforum.com/showthread...hreadid=545599

Removing blanks / sorting - common methods not applicable
 

I've done it with VBA, but I don't have it available here. Maybe someone
else can assist.
"Dark_Templar"
wrote in message
...

I know several ways to remove blank cells, however none really works
here.

Autofilter doesn't work because I need a dynamic method

-=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))-
..doesn't work either because it doesn't consider the sorting.

In the attached file: The number 40658 has certain dates assigned to
it.
If aboves methode is being used, the dates are all messed up.
(Autofilter would do the trick , but its not dynamic)

How do I dynamically remove the blank cells/rows and keep the sorting
intact?


+-------------------------------------------------------------------+
|Filename: Example.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4811 |
+-------------------------------------------------------------------+

--
Dark_Templar
------------------------------------------------------------------------
Dark_Templar's Profile:
http://www.excelforum.com/member.php...o&userid=33279
View this thread: http://www.excelforum.com/showthread...hreadid=545599