Cut/move row if data in one Column

How about a plain sort on column B. Would that be satisfactory for you?

""nutsch:
How about a plain sort on column B. Would that be satisfactory for you? ""

I thought of this earlier but when i tried it Manually it does not keep a perfect order. I would like the rows to remain in the same order as they were through out the table but just at the bottom. With sorting the rows that are moved change positions with each other a bit. Not horrible just looking for something nicer.

But i just thought of something i could give a value to a new column like "f" 1-1000 then do a sort based off the temporary value


""rspahitz:

Try changing the C:C reference to a subset that contains only the data you want to copy and see if it works.  If everything in column C that you want contains data (with no blank cells) you can create  a macro to start at the top of the data and select down to the end of the data block before copying it.
Then the rest of your code should work. ""

Not sure what you mean by subset, something like copy(a1-a15) to last used row?

nutsch: I was typing my response when you posted


Yeah that is what i tested manually but

        .SetRange Range("A1:D10")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

Resorts these rows by value which i am trying to avoid. I want them placed at the bottom but remain in the relative positions to each other.

Rock            564      Help
Orange            4565      Help
Rock            456546      Help

Got it!  

I can sort by cell color, Changing cell color of any cell with data in column C, then sort by color, then remove color. Perfecto!

Nutsch

Got me on the right path. His answer was correct but i needed a bit more. Awarded more points since this helped me more

rspahitz
I am sure his answer is correct but i am unclear by his response what needs to be done. This is my limitation with excel not his answer.



Thanks to both!

Here is the Sort i ended up with.

 

  Range("F:F").SpecialCells(xlTextValues).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599963377788629
        .PatternTintAndShade = 0
    End With

    Range("B1:H82").Select
    Range("H82").Activate
    ActiveWorkbook.Worksheets("GLALL").Sort.SortFields.Add Key:=Range("F1:F82"), _
        SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("GLALL").Sort
        .SetRange Range("B1:H82")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Select allOpen in new window

Thanks for the follow-up Daniel.

My comment was about taking only the cells in column C that contain data (rather than ALL of column C) and appending them to the end of column A.

Glad you got a working solution!