Sort by named range or blocks of data with merged cells?

[donkey33]

Hi, just wondering if it's possible to sort data by named range when using merged cells. Below image gives a rough idea of what I mean.

qppbaq.png

So range from A1 to F5 and named DATA1, range from A6 to F10 is DATA2 and so on. I want to sort by a numeric value in column C. There are many of these blocks of data one underneath the other as shown in the pic but is there a way to treat the range as one entire row, sort by column C and have all the blocks move correctly as is? Excel obviously throws the cannot sort with merged cell error and I need the entire range/block of data to move as one piece.

Is that possible?

Thanks

[xladept]

Hi Donkey,

Welcome to the Forum! How many of those records do you have?

If you'll: Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I'll take a stab at it

[donkey33]

Hi xladept, thanks for the welcome

I'll put it together shortly and upload for you.

Thanking you

[donkey33]

Hi xladept, I have uploaded a basic sheet of what I'm trying to do.

Basically, just want to sort each group of rows (indicated by color) by the rank figure and have all associated data within those rows move also. So if sorting rank from largest to smallest, name 5/6 should be at the top with all the resulted data 3's.

Any questions, please let me know and appreciate you taking a look at this.

Thanks

[xladept]

I'll take a look tomorrow unless someone solves it by then

[donkey33]

Thanking you

[xladept]

I'm not sure I matched your gray, but try this:

Please Login or Register  to view this content.

[donkey33]

Color definitely isn't an issue. I just added it to highlight the block of rows but I'll most likely take it out.

I'm stuck at work but I'll be trying this out when I home. Much appreciated and I'll post once I try it out.

[donkey33]

Just tested it and it works with the test example. Only problem I'm having now is applying it to the actual data I have. Keep getting type mismatch or variable errors (I'm not familiar with this kind of stuff).

The test example was just something I created that doesn't actually mirror the actual data. I probably should have just edited that information instead and uploaded that. Might be an easy fix if you don't mind altering it a little to match that data?

Also, I have a space in between each block of data. Is there a way to preserve this? If not, I could remove it I guess. It's just for spacing really.

One other thing, is there a way to have the column and row sizes stay the same size? They seem to default to the original excel sizes and not hold firm on the size already set throwing the merges way off size.

Thanking you, this is much appreciated.

[xladept]

It threw the type mismatch because the Key was no longer in "F" - it's now in "J"

Try this:

Please Login or Register  to view this content.

* If you have a lot of records (10,000 or more), I should change the sort

[donkey33]

Sorry, had to go away on business yesterday but I'm home tomorrow and will try this. Thanks again and many thanks when I get back if it works

[donkey33]

Seems to work well with the test. I couldn't get it to work with my actual data though. Something about object variable so I copied the test sheet over and will need to replace what's in there and use that instead.

Is it an easy fix to lengthen the number of blocks moved? For example, I have 1-7 ranks and when ordered, there are 7 but when I have 8 or more, after the macro is run, it's only showing the top 7 and not all items. So for a top 30 (for example), it's just showing the top 7 records and that's it.

How do I fix that?

Thanks

[xladept]

There shouldn't be any problem - what is different about the actual data?

[donkey33]

Nothing at all to be honest. I actually copied and pasted from there for the test data so that was the source. I just changed the actual figures and team names otherwise they're identical.

I can't figure out why it didn't apply across as once I copied the sheet from the test file to the real file, it worked. No idea why but it works.

Just can't figure out how to have it show more than the 7 ranks. For a top 30, it's only showing the lowest/highest 7 and that's it. Is it easy to make it show all 30? What flag do I need to change?

Thanks

[xladept]

Most probably you have two blank rows between the 7h and 8th entries

[donkey33]

Nah, I just copied and pasted the same upper block (so 14 entries) and all with the same single row between all of them.

Any ideas?

[xladept]

Yeah,

I'm using a dictionary in that program so duplicate data will be ignored - try it for real

[donkey33]

Spot on man. With the real data or even copied data, as long as the rank key was different, it worked and organise every single entry.

Thank you so much for this. I would have never been able to work this out and the only option I had was to just create a normal lengthy width table housing all the data. This way I can have certain bits vertically presented which is much better.

Again, very much appreciated.

[xladept]

You're welcome!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[donkey33]

Done, marked as solved

[donkey33]

Me again

Two areas I'd like to touch up if possible. After entering all my data, having two issues.

1. Lookup values don't move and just give a reference error. The ranking number is fed from a lookup (simply, equals a cell from another sheet where the calculation is). When processing the macro, is there a way to copy the value of the cell and not the formula?

2. I have images in a column of cells. The images move fine but the macro somehow copies all the images in the order they are first, then pastes the correctly ranked images over the top of the previous pasted images. So two images, one over the other. Looks like when the new sheet is created, it copies worksheet 1 and then clears contents but it's not clearing the images, only the data. Any way to also clear the images?

Thanks

[xladept]

Hi donkey,

If this doesn't work then I'll need to see a representative sample - there were no formulas or shapes on the one submitted

Please Login or Register  to view this content.

[donkey33]

Works perfectly, thank you again

I tried a pictures.delete function and it kind of worked but also deleted all the images from the original source. Yours is much better as it leaves the source alone and deleted everything from the newly created sheet.

Much appreciated again. Hopefully this is the last thing I need adjusting, lol. Seems to work as I want it to now so fingers crossed.

[xladept]

You're welcome! Glad it worked - but, any other changes would, probably, require code adjustment

Don't cross them twice!

[donkey33]

Cross them once more?

Everything is working well but only the ranking figure values are copied across. All the data is still showing reference lookup errors.

Is it easy to copy values for all data also?

[xladept]

Try this one - I don't clear the shapes because I don't paste them:

Please Login or Register  to view this content.

[donkey33]

It's close but still not quote there.

I'm using this code right now.

Please Login or Register  to view this content.

This works perfectly except all other data in the colums doesn't copy across and I get reference errors as they are lookups. So I grabbed part of the latest code above and replaced the 4th and 5th line from the bottom

Please Login or Register  to view this content.

With this

Please Login or Register  to view this content.

So now the reference lookup errors are fixed and it pasts the values only but the images in the columns have disappeared. So I have two codes but one has images, the other pasted values but I can't seem to figure out how to combine the two to get both.

Is it an easy fix?

Thanks

[xladept]

Maybe:

Please Login or Register  to view this content.

[donkey33]

Thanks, that's now perfect. Not sure how to say thanks enough

[xladept]

Hi Donkey,

Glad it's working - you can thank me by giving me by giving me reputation points - just click on the little star at the bottom left

[donkey33]

Done.

Thanks again

[xladept]

You're welcome and thanks for the rep!