INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Move Cells around

Move Cells around

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

CODE

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

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

Is there a better way to do it?


---- Andy

There is a great need for a sarcasm font.

RE: Move Cells around

CODE

Range("A8").PasteSpecial xlPasteAll 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Move Cells around

(OP)
I get error 1004 PasteSpecial method of Range class failed sad


---- Andy

There is a great need for a sarcasm font.

RE: Move Cells around

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Move Cells around

(OP)
I see.
Excel is not crazy about Cut, but Copy works smile


---- Andy

There is a great need for a sarcasm font.

RE: Move Cells around

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Move Cells around

(OP)
I wish it would be that simple. smile
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.... smile


---- Andy

There is a great need for a sarcasm font.

RE: Move Cells around

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Move Cells around

(OP)
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.

RE: Move Cells around

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Move Cells around

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

RE: Move Cells around

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

CODE -->

' 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

RE: Move Cells around

(OP)
Skip,
Yes, but... (there is always a 'but' sad ). 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close