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)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
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?

I've done it with VBA, but I don't have it available here. Maybe someone
else can assist.
"Dark_Templar" <[email protected]>
wrote in message
news:[email protected]...
>
> 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)+1>ROWS(BlanksRange)-
> COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
> (IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
> 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
>