Move Non-Zero row entries up to occupy Zero row entries. Rows deletion not allowed

[chancw]

Excel 2003
Raw data is given in Table 1 which consists of some zero row entries.

RAW Data (Table 1)

Time Price Volume Type
8:58:19 2.77 4,000 Buy Up

9:00:00 2.76 10,000 Sell Down


9:00:02 2.76 15,000 Sell Down
9:01:11 2.75 7,800 Sell Down


There is a need to move the non-zero row entries to occupy the zero row entries. Deletion of zero row entries is not allowed.
The final table looks like the following:


PROCESSED Data (Table 2)

Time Price Volume Type
8:58:19 2.77 4,000 Buy Up
9:00:00 2.76 10,000 Sell Down
9:00:02 2.76 15,000 Sell Down
9:01:11 2.75 7,800 Sell Down


Attached worksheet template for reference.

Help is appreciated.

[davesexcel]

Try this,

Please Login or Register  to view this content.

Rows are not deleted, just the blank cells.

[chancw]

Thanks, but the blank rows cannot be deleted.

[sktneer]

Try this........

Please run the following code on sheet Test. The code is stored on module2. See if this is what you want.

Please Login or Register  to view this content.

Please see the attached sheet.

[chancw]

Thanks, it works.

But what if the Table starts at Column "H" Row "6" instead of the current Column "A" Row "1"?

[sktneer]

If your table headers in row 6 i.e. table starts from H6, use the following code......

Please Login or Register  to view this content.

[chancw]

Thanks, the code works fine but the processing takes about 1 minute to run the attached data sample.

Is there any way to make the processing faster?

[sktneer]

Its because your requirement is unique as you don't want to delete the blank rows. I don't know any faster method. But instead of running the code at once, better you debug the code by pressing F8 to see each step involved in the process. Because after shifting the non blank cells data to the the blank cells up, the code has to recheck the data again from the beginning, therefore so many steps are involved.

[chancw]

Thank you for your kind explanation. I understand better now. By the way, I will mark this thread as solved.

[jindon]

Thanks, the code works fine but the processing takes about 1 minute to run the attached data sample.

Is there any way to make the processing faster?

try the attached

[chancw]

Jindon,

This is an interesting idea!

You try to get around the limitation by transferring the contents one by one to a table in another worksheet.

I'm just thinking if I can extend your idea by transferring the contents to a table WITHIN the same worksheet.

Do see the attached excel worksheet.

[jindon]

Change to

Please Login or Register  to view this content.

[chancw]

jindon,

Thanks, it works much faster now!

But the color of the volume text values in original table(Table 1 Column C) did not get copied over to the new table (Table 2 Column I)

[chancw]

Jindon,

I encountered a new issue recently. When the raw data are available as follows:

Time Price Volume Type
10:10:05 $3.91 93,000 Sell Down
10:10:05 $3.91 2,000 Buy Up
10:10:05 $3.90 1,000 Sell Down

the processed data becomes:
Time Price Volume Type
10:10:05 $3.91 94,000 Sell Down
10:10:05 $3.91 2,000 Buy Up

which is not supposed to. It seems that sometimes the algorithm disregards the price when the "Time" and "Type" are the same. Is it possible to correct this issue?

Regards