Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Auckland, North Island, New Zealand
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    effect of moving cells (Excel 2000)

    I understand that if I move a cell or delete a column the VBA programming doesnt reflect the change.
    I realise the answer is to name the cells using Insert/Name which I will do in future.
    Is there a quick solution if I want to move cells/ delete columns in a project already written?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: effect of moving cells (Excel 2000)

    I would:

    - Name the ranges used in the project
    - search and replace in the VBA code using the addresses

    But that is no watertight solution...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: effect of moving cells (Excel 2000)

    I don't understand your question. What do you mean by "... VBA programming doesn't reflect the change."? Do you mean that you can't undo something in Excel if the change was done in code? Yes, that's true (although there are awkward work arounds but they involve yet more code).

    So you want to move cells (actually cells can't move, but their contents can), delete cols in an existing project. Can't you just write code to do that or I'm totally missing something. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: effect of moving cells (Excel 2000)

    I think that he means that code like this:

    Worksheets("Sheet1").Range("A1:C100").Copy

    Does not change when a user inserts or deletes rows and or columns in that range.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Location
    Auckland, North Island, New Zealand
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: effect of moving cells (Excel 2000)

    Yes you are right. That is exactly what I meant

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: effect of moving cells (Excel 2000)

    Then you need to calculate the range you want to operate on in the VBA routine. Following Jan Karel example, if you want to copy from cell A1 down through the last data entered in column C, then you could use something like this:

    <pre> Range(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet1").Range("C65535").End(xlUp)).Co py
    </pre>


    However, you have to be able to define what range you want to use in a way that the code can define it.
    Legare Coleman

Posting Permissions

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