Move Cells around 1

[Andrzejek]

I need to move some data in Excel. So I:
[ul][li]Insert an empty Row where I need the data to be[/li]
[li]Cut data I need to move[/li]
[li]Paste it where I need it, into the new empty inserted Row[/li]
[li]Delete the (empty) Row where data used to be[/li]
[/ul]
With this code:

Range("A8").EntireRow.Insert
Rows("26:26").Cut[blue]
Range("A8").Select
ActiveSheet.Paste[/blue]
Rows("26:26").Delete Shift:=xlUp

The code works just fine, I'm just not happy with the [blue]BLUE[/blue] portion of it.
Not crazy about [tt].Select[/tt] statement.

Is there a better way to do it?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Range("A8").PasteSpecial xlPasteAll

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

I get error 1004 PasteSpecial method of Range class failed :-(


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Range("A8").EntireRow.Insert
Rows("26:26").[b]Copy[/b]
Range("A8").PasteSpecial xlPasteAll
Rows("26:26").Delete Shift:=xlUp

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

I see.
Excel is not crazy about [tt]Cut[/tt], but [tt]Copy[/tt] works


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Why can’t you SORT into the desired order? Much more preferable than your method.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

I wish it would be that simple.
This is a report for a user. I grab the data from DB and present it the way user wants me to. Nothing straight in this process. A lot of summaries, previously done by hand.

I totally agree with your approach, but need to do it that way.
Pesky users....


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Then by what criteria does the data from row 26 get to row 8?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Andrzejek]

Rows 1-6: various headers (yes, I know - crazy)
Rows 7-18: various Major Fund data, but the same logic for all Rows to get it from DB and display
Rows 19-25: other Fund data, different logic to get it
Rows 26-27: additional data, another logic to get it

Now, one of the 'other Fund' data needs to be after one of the 'Major Fund' data. That's where Copy/Paste/Delete comes in. A lot easier than messing with the different logics to get it, believe me.

Rinse and repeat - staring from Row 34 (after another 3 rows of headers)
Same data, displayed in different way.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Something identifies the “Major Fung” data as Major Fund, and something identifies the “Other Fund” data as
Other Fund else other, yes?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

You can slightly rewrite your original code for rearranging rows, with Cut and Paste only. This is eqivalent to rearranging rows with SHIFT key pressed, no added rows, no need to delete any.

combo

 

[combo]

A code to my previous post, for active sheet, two swaps:

' swap rows 10 and 11
' cut row 10
With ActiveSheet
    .Rows(10).Cut
    .Rows(12).Insert Shift:=xlDown
' or cut row 11
    .Rows(11).Cut
    .Rows(10).Insert Shift:=xlDown
End With

combo

 

[Andrzejek]

Skip,
Yes, but... (there is always a 'but' :-( ). There is a lot of other logic in getting the data user wants for any Fund records, either Major or Other or Else. If I would order the rows in Excel right from the start - and trust me, I would if I could - that would create a spaghetti logic nobody would touch, including myself. The code is convoluted enough as it is, but with the right amount of separate logical procedures and comments is still manageable.

combo,
Thanks for the hint. For now I will stick with what I have, but I will save your suggestion for future needs.


---- Andy

There is a great need for a sarcasm font.