Insert Line In A Sort When Data Missing?

**DonG** · 10-15-2011, 01:37 AM

When doing a Sort (numerical Sort) on data that is populated in a Col is there a way for Excel to skip a line when data is missing? i.e. If Sorting items 1-10 and item 4 is missing, leave line 4 blank but continue the rest of the Sort.

**Andrew-R** · 10-15-2011, 01:41 AM

Would it be acceptable to have a macro which sorts the data and then inserts blank lines for missing values?

**watersev** · 10-15-2011, 06:10 AM

hi, DonG, please check attachment, run code "test"

**Andrew-R** · 10-15-2011, 06:19 AM

Watersev, your code doesn't work, because it sorts each block of number independently, so you end up 1,4,<gap>,2,3,5,<gap>,<gap>,12,14,16,17,18 when, presumably, the OP wants returned is 1,2,3,4,5,<gap>,<gap>,<gap>,<gap>,<gap>,<gap>,12,<gap>,14,,<gap>,16,17,18.

Maybe we should wait until the OP clarifies their requirements a bit.

**DonG** · 10-15-2011, 12:18 PM

OK..I've got a macro that is not very clean but sort of works. Populate Col A as follows. 4,6 and 9 are missing. All the file extensions end with this format.

(XX01)
(XX02)
(XX03)
(XX05)
(XX07)
(XX08)
(XX10)

In the macro below I've set the variable LastRow to +10. If I just use LastRow the macro doesn't loop far enough. I don't anticipate ever having a situation that would have more than 10 empty files. Range ("D1") is a helper cell to trim the file extension.

Please Login or Register  to view this content.

I'm sure someone out there could really clean this up a bit....TIA.

**Andrew-R** · 10-15-2011, 12:57 PM

What's in column A to start with? I'm a bit lost as to why you're taking Right and Left portions of the values.

Do you have any data in any other columns to be sorted along with column A, or is this just about getting column A into order?

**DonG** · 10-15-2011, 02:20 PM

Originally Posted by Andrew-R

What's in column A to start with? I'm a bit lost as to why you're taking Right and Left portions of the values.

Do you have any data in any other columns to be sorted along with column A, or is this just about getting column A into order?

Col A was used only to simplify this example and will be changed appropriately when I get this working.

The reason for taking right and left portions was to trim the data to a two digit number.

This is only a small part of a larger macro that follows this sequence:

Populates a list of file names from a drive into Col A.
Uses Text to Col's function to isolate a portion of the file name. (i.e. "(XX01)")
(That portion of the file name is what I used for this example)
Sorts Col A:H based on the portion of the file name.
(This is where this sub will be called)
Then does a few other things not important to this discussion.

I used this data in Col A only for this example.

As I stated, what I have so far works fine with the exception of having the arbitrary number in there. (10)

I guess since it works, I'll call this one solved.....Thanks for your input, you got me thinking in a different direction and that worked.

Don