• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2010 can't see my macro. I can't assign an icon to it in ribbon.

S

sdfjh87687

New Member
Hi
I'm excel beginner and not often user. I googled some easy macro, which easy and effectively provides me to move up down rows in already filled sheet and also columns left right.
I store all my handy macros in here in my windows 7: C:\Users\a\AppData\Roaming\Microsoft\AddIns\MyAddins.xlam

I was adding new macro as usuall. Insert > Module > paste code. save.
Then I went into ribbon customizations, but excel didn't offer me my macro. There were just all old ones.

Here is code for problematic new macro:
Code: 
Sub MoveRowsOrColumns(direction As String)
    Dim rOriginalSelection As Range

    Select Case direction
    Case "up", "down"
        Set rOriginalSelection = Selection.EntireRow
    Case "left", "right"
        Set rOriginalSelection = Selection.EntireColumn
    Case Else
        Debug.Assert False
    End Select

    With rOriginalSelection
        .Select
        .Cut
        Select Case direction
        Case "up"
            .Offset(-1, 0).Select
        Case "down"
            .Offset(rOriginalSelection.Rows.Count + 1, 0).Select
        Case "left"
            .Offset(0, -1).Select
        Case "right"
            .Offset(0, rOriginalSelection.Columns.Count + 1).Select
        End Select
    End With
    Selection.Insert
    rOriginalSelection.Select
End Sub

Thank you for any help !
 
This particular macro has 1 argument required to run, the variable "direction". Thus, it can never be directly called as the starting point; only as a subsequent macro to another. You would need to either create 4 other macros (one for each direction), or re-write the beginning of this one so that it doesn't take an input argument.
 
Thanks for reply. I have no idea about programming things. I have learned just to save useful macros into add-in file for future.
If anyone would be so kind to modify("re-write the beginning of this one so that it doesn't take an input argument") that macro as it was it's original meaning, it would be awesome.

Thanks for understanding
 
Sure thing. Give this a shot. Code will now prompt you to choose which way you want to go.
Code: 
Sub MoveRowsOrColumns()
    Dim rOriginalSelection As Range
    Dim direction As String
   
startOver:
    direction = LCase(InputBox("Which direction would you like to go?" & vbNewLine & "Choose from: up/down/left/right", "Direction"))
   
    'check if user cancelled
    If direction = "" Then Exit Sub
   
    Select Case direction
    Case "up", "down"
        Set rOriginalSelection = Selection.EntireRow
    Case "left", "right"
        Set rOriginalSelection = Selection.EntireColumn
    Case Else
        MsgBox "That was not a valid choice. Please try again."
        GoTo startOver
    End Select

    With rOriginalSelection
        .Select
        .Cut
        Select Case direction
        Case "up"
            .Offset(-1, 0).Select
        Case "down"
            .Offset(rOriginalSelection.Rows.Count + 1, 0).Select
        Case "left"
            .Offset(0, -1).Select
        Case "right"
            .Offset(0, rOriginalSelection.Columns.Count + 1).Select
        End Select
    End With
    Selection.Insert
    rOriginalSelection.Select
End Sub
 
I was hoping to work differently. More handy. Let me explain:

1, Select whole row(s) or column(s).
2, Press macro icon (nothing will pop up).
3, Press on keyboard one or more times Down/Up arrow keybutton for row(s) selected, Left/Right arrow keybutton for column(s) selected.
4, After each press, it will move selected stuff instantly. Immediate response from that macro.
5, Press Escape button, to stop macro executing.

Is possible ?

Thank you !!
 
Nope, not really.

You could just do the whole thing manually; it's not a complicated macro.
  1. Select your row/column
  2. Ctrl + x to cut
  3. Move to where you want to go
  4. Ctrl + + (Ctrl & plus symbol) to insert and paste
IMO, much simpler than setting up macro, linking to QAT, and then doing multiple clicks.
 
sorry for long delay, had pc problems.
i did like you wrote:
1, select whole row
2, ctrl+x (cut)
3, select some other whole row
4, ctrl+v (paste)

result: empty row left after cutted row and destination row got replaced by cutted row during paste. so blank + lost row.
 
It should be...
1. Select entire row
2. Ctrl + x (cut)
3. Select destination row
4. Ctrl & "+" (key in number pad, not +/= on main section)

It's not paste operation but rather, insert copied/cut row.
 
It should work. Right click on the entire row where you want to insert cut row. Then it should give you option to "Insert Copied Cells". It should show "e" with underscore to indicate it as short cut key (it may be different key).
 
You must log in or register to reply here.
Back
Top