Results 1 to 4 of 4

Selection.Insert Shift:=xlToRight Acting Odd

This is a discussion on Selection.Insert Shift:=xlToRight Acting Odd within the Excel Questions forums, part of the Question Forums category; I have a macro that processes one or more sheets of data. At one point it finds a certain column, ...

  1. #1
    Board Regular Bill_Biggs's Avatar
    Join Date
    Feb 2007
    Location
    Planet Texas of the Texan Empire
    Posts
    1,216

    Default Selection.Insert Shift:=xlToRight Acting Odd

    I have a macro that processes one or more sheets of data. At one point it finds a certain column, copies the occupied cells in the column, selects column A, and inserts the copied column here.

    Here is that portion of the macro:

    Cells.Find(What:="CpuFNode*:MemClkFreq", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate
    ActiveCell.Select
    Range(Selection, Selection.End(xlDown)).Copy
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight

    The first time through, the macro performs flawlessly. But the second time through, the new column is pasted all the way down the column, using all 65000 rows and not just the 20-150 cells that it did microseconds before.

    Has anyone seen odd behavior like this before? Is there something I can do to remedy it?

    Thanks,

    Bill Biggs
    Thanks,

    Jesus loves me. If you knew me, you would know how amazing that is. And he loves you too! How amazing is that?!
    t

  2. #2
    Board Regular
    Join Date
    Feb 2005
    Location
    Melbourne (Australia)
    Posts
    553

    Default

    The second time through there is no other cells below the cell containing "CpuFNode" and so the selection.end(xlDown) goes all the way to the bottom of the worksheet......

    That appears to be the problem. Maybe you need a command something like:

    If activecell.offset(1,0) = "" then activecell.offset(-1,0).activate


    Though this would get into trouble at the top of the row...
    Philby

    EDIT: No, I've gotten confused. But for some reason the select to end command is goign to the bottom. I just can't tell why. How many instances of the "CPU" thing are there?
    "I'm almost sure I'm not mad..." - Stoppard

  3. #3
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,115

    Default

    Range(Selection, Selection.End(xlDown)).Copy
    I think this line is the issue. If the next time you find the search item it's the only thing in the column, Excel will select down to the end of the column (not the end of the data).

    Are you trying to copy a set number of cells across? If so, you could use

    Code:
     Selection.Resize(25,1).Copy
    To copy 25 rows... adjust as required.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  4. #4
    Board Regular Bill_Biggs's Avatar
    Join Date
    Feb 2007
    Location
    Planet Texas of the Texan Empire
    Posts
    1,216

    Default THanks

    Thanks for the tips. I'll see what I can do with them.
    Thanks,

    Jesus loves me. If you knew me, you would know how amazing that is. And he loves you too! How amazing is that?!
    t

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com