Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Is it easy to move a block of cells to another location on the same sheet and leave where it came from as "normal" cells with no formatting or totalling, if you understand what i am trying to do.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can move a block of cells in different ways:

    - Select a block of cells.
    - Move the mouse pointer to any edge of the block so that it becomes a four-pointed arrow.
    - Drag the block with the left mouse button held down to its new location.
    - If you drag with the right mouse button held down, you'll get a popup menu with options when you release the mouse button.

    or

    - Select a block of cells.
    - Press Ctrl+X to cut the cells to the clipboard. You'll see a blinking border around the block.
    - Click in the upper left cell of the destination.
    - Press Ctrl+V or Enter to paste the cells.

    In both cases, the original block of cells will have the default formatting of the Normal style.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I meant in automation code from Access.
    I would like to keep all the attributes of the row/cols i move too.
    Is this possible?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You can use code like this:

    xlWsh.Range("B3:E15").Cut Destination:=xlWsh.Range("H8")

    where xlWsh is a variable that refers to a worksheet in an Excel workbook.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Thank you Hans

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='771130' date='18-Apr-2009 09:11']You can use code like this:

    xlWsh.Range("B3:E15").Cut Destination:=xlWsh.Range("H8")

    where xlWsh is a variable that refers to a worksheet in an Excel workbook.[/quote]
    Is there a way to address that by cells rather than by Range?
    Something like:
    xlWsh.Cells(2,3:5,15).Cut Destination:=xlWsh.Cells(8,8)

    I always tend to address cells in automation by number rather than letters.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of xlWsh.Cells(2,3:5,15) use xlWsh.Range(xlWsh.Cells(2, 3), xlWsh.Cells(5, 15))

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='771200' date='19-Apr-2009 09:53']Instead of xlWsh.Cells(2,3:5,15) use xlWsh.Range(xlWsh.Cells(2, 3), xlWsh.Cells(5, 15))[/quote]
    Thank you very much Hans

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='771200' date='19-Apr-2009 09:53']Instead of xlWsh.Cells(2,3:5,15) use xlWsh.Range(xlWsh.Cells(2, 3), xlWsh.Cells(5, 15))[/quote]
    I have just tried this and it throws an error:
    Error:1004 Cannot change part of a merged cell

    I ran the following command:
    mySheet.Range(mySheet.Cells(105, 1), mySheet.Cells(112, 14)).Cut Destination:= mySheet.Cells(16,14)

    Does this mean i cannot move merged cells?

    How do i overcome this problem?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In general, I would avoid the use of merged cells. They always cause problems when you want to manipulate cells in VBA.

    As an alternative, you can use "Center across selection" as horizontal alignment.

    If you want to set this in code:

    mySheet.Range(mySheet.Cells(1, 1), mySheet.Cells(1, 5)).HorizontalAlignment = xlCenterAcrossSelection

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='HansV' post='771681' date='22-Apr-2009 16:29']In general, I would avoid the use of merged cells. They always cause problems when you want to manipulate cells in VBA.

    As an alternative, you can use "Center across selection" as horizontal alignment.

    If you want to set this in code:

    mySheet.Range(mySheet.Cells(1, 1), mySheet.Cells(1, 5)).HorizontalAlignment = xlCenterAcrossSelection[/quote]
    Thank you, but what does that do?

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    [quote name='patt' post='771713' date='22-Apr-2009 12:18']Thank you, but what does that do?[/quote]
    It gives a similar appearance to merging cells, without actually merging them.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •