Macro to turn all blank cells to 1000 and make them white

[thelegazy]

As the title says, I need some help with a macro to make all the blank cells in different sections to the value of "1000" and turn the font to white.
I already have a code for prioritizing values in different sections, but I would like the values turned to 1000, so that they come up last. Below is the code for my button:

Please Login or Register  to view this content.

I want to be able to add to this button so it will do my requested part first, then will follow with what is there at the moment. So basically people will insert their priorities in column A (1, 2, 4, 10, 6) and the code above basically prioritizes it, however I want the users to be able to leave some cells blank and have those moved to the bottom of the section. I thought this would be easiest if I replaced the blank cells with a value of 1000 and make it white so it can't be seen and the prioritize and since I'll never have 1000 priorities it will always show up last. Maybe someone has a better idea?

-Thanks

[Norie]

This code will put 1000 in each blank cell in column A and make them white.

Please Login or Register  to view this content.

[thelegazy]

I can't seem to get it to work. If you put values in each different section under column A in the attached excel sheet and run the code in the original post you will see that it organizes everything lowest to highest. I would like to add a condition that anything that is left blank be sent to the bottom. I thought changing all the blank values to 1000 would do that, but it doesn't seem to be working.

[Norie]

I didn't think the sort would work either but the code I posted does work with your worksheet.

I think one problem with the sort might be that you don't have more than one area any more, you'll just have one contiguos area.

[thelegazy]

But if I use the original code it can sort by each section. Can I have it just put 1000s in each blank in each section?

[Norie]

I know your original code sorts the sections but if you put 1000 in all the blanks in column A you don't have separate sections anymore.

What is it that's wrong with the original sorting code?

When I run it the sections are sorted separately.

[thelegazy]

Oh sorry, I might have mis said what I wanted. I wanted to put 1000 in the blank cells in each section, not the entire column. The original code works fine except that I want the blank cells to go to the bottom (but since they are considered 0s they go to the top).

[Norie]

What blank cells in the sections?

There don't apper to be any.

[thelegazy]

Oh sorry I uploaded the a different file. I just deleted random cells in each section, sorry about that.

[thelegazy]

Bump, can someone lend a hand? Thanks!

[Norie]

What exactly do you need a hand with?

The original code works fine, it sorts the sections separately.

[thelegazy]

How do I get it to insert 1000 in the blank cells and then turn them white? (I know the sheet doesn't have any blank cells within the sections, but if there were how would I do it?

[Norie]

I've already posted code to do that, and as you found out doing that doesn't seem to help.

[thelegazy]

So this isn't possible?

[Norie]

Sorry, what isn't possible?

I posted code that fills the blanks with 1000 and turns them white, so that's possible.

[thelegazy]

It's not possible to have 1000 posted in each blank cell within each section rather than throughout the entire column? (I know the original file has no blanks, but lets say there was a blank one in a few sections). And also have them with a white font?

[Norie]

Yes that should be possible but I don't see how it would make a difference.

Is there a problem withe he sorting when there are blanks?

[thelegazy]

Yes that should be possible but I don't see how it would make a difference.

Is there a problem withe he sorting when there are blanks?

The blanks are moved to the top of the sections, but I want to move all the blanks to the bottom of each section.

[Norie]

Can you attach a workbook with the blanks?

[thelegazy]

Please see the attached file with the blanks in each section under column "A".

-Thanks

[chakravarthysm]

thank u very much,i got the solution....,

[chakravarthysm]

thank u very much....

[thelegazy]

Norie,

I was wondering if you had a chance to look at the sample workbook I posted.

-Thanks

[nilem]

maybe so

Please Login or Register  to view this content.

[thelegazy]

Nilem, thanks so much it worked perfectly!

Thank you also to Norie for sticking with me and trying to come up with a solution, I really appreciate it.