Cut/move row if data in one Column

Daniel_P67
Daniel_P67 used Ask the Experts™
on
I have some data that i want to move but am running into an issue, i guess you cant move multiple rows at once in Excel VBA. I was trying to use Range("C:C").SpecialCells(xlTextValues).entirerow.cut then paste the data at the bottom of my list with something like Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues. But excel is throwing a fit. No matter what paste command i use it will not paste multiple rows.


The only other way to do this that i know of is with a Search in column C and if data is found then move it to the bottom, then loop this a number of times. Is there an easyier way to handle moving the data. I have Attached a sample workbook with a before and after table for reference.

Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I suspect the problem is that you're trying to copy all of column C rather than a subset, so when you go to paste it may not like having an entire column squeezed into a column where it can't fit an entire column (i.e. anywhere except the first row)

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.
Top Expert 2008

Commented:
How about a plain sort on column B. Would that be satisfactory for you?
Top Expert 2008
Commented:
Something like this: sort then cut and past

    ActiveSheet.Sort.SortFields.Add Key:=Range("C1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveSheet.Sort
        .SetRange Range("A1:D10")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Range("A1:D10").Columns(3).SpecialCells(xlCellTypeConstants).EntireRow.Cut
    Cells(Rows.Count, 1).End(xlUp).Offset(1).EntireRow.Insert shift:=xlDown

Open in new window


Thomas
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
""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?

Author

Commented:
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

Author

Commented:
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!

Author

Commented:
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!

Author

Commented:
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

Open in new window

Commented:
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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial