Selection.Delete Shift:=xlToLeft (what's the meaning of this?)

Discussion in 'Microsoft Excel Programming' started by markx, Mar 13, 2006.

  1. markx

    markx Guest

    Hello,

    Could someone explain to me what is the meaning of the VBA expression:
    Selection.Delete Shift:=xlToLeft

    Intuitively, I would suppose that it should delete the selection, but why
    there is this "Shift" part coming after?

    The whole part of the code I try to analyze goes like this (and it looks
    like a big garbage for me):

    (...)
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Confirmations").Select
    Range("BA22").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
    _
    False, Transpose:=False
    Columns("BA:BD").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("BC:BD").Select
    Selection.Delete Shift:=xlToLeft
    Columns("BD:BF").Select
    Selection.Delete Shift:=xlToLeft
    Columns("BE:BZ").Select
    Selection.Delete Shift:=xlToLeft
    Range("BA22:BD55").Select
    Selection.Copy
    Range("A22").Select
    ActiveSheet.Paste
    (...)

    In particular, I can't see what is the goal of selecting first BA:BD, then
    BC:BD, then BD:BF and finally BE:BZ...
    Is it me that don't understand something or the code should be written
    differently? (hint: it's still functionning correctly and it is not deleting
    the data pasted in BA22 (lines 4-8 of the code)).

    Many thanks for any explanations!
    Mark
     
    markx, Mar 13, 2006
    #1
    1. Advertisements

  2. The line that selects the columns does just that. The line that deletes the
    selection deletes the selected columns. The shift to the left part simply
    shifts the remaining columns to the left. That code is not necessary since
    the shift to the left is the default. Note that if you delete a column, the
    column to the right will move left and occupy the space where the deleted
    column was. The new column will also take the column letter of the deleted
    column. Perhaps that is where you are confused. In other words, if the
    sheet is blank and you delete a column or group of columns, you will not see
    any change at all.
    Exactly what is that you want this code to do? HTH Otto
    "markx" <> wrote in message
    news:elRvA$...
    > Hello,
    >
    > Could someone explain to me what is the meaning of the VBA expression:
    > Selection.Delete Shift:=xlToLeft
    >
    > Intuitively, I would suppose that it should delete the selection, but why
    > there is this "Shift" part coming after?
    >
    > The whole part of the code I try to analyze goes like this (and it looks
    > like a big garbage for me):
    >
    > (...)
    > Range("A3").Select
    > Range(Selection, Selection.End(xlToRight)).Select
    > Range(Selection, Selection.End(xlDown)).Select
    > Selection.Copy
    > Sheets("Confirmations").Select
    > Range("BA22").Select
    > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
    > _
    > False, Transpose:=False
    > Columns("BA:BD").Select
    > Application.CutCopyMode = False
    > Selection.Delete Shift:=xlToLeft
    > Columns("BC:BD").Select
    > Selection.Delete Shift:=xlToLeft
    > Columns("BD:BF").Select
    > Selection.Delete Shift:=xlToLeft
    > Columns("BE:BZ").Select
    > Selection.Delete Shift:=xlToLeft
    > Range("BA22:BD55").Select
    > Selection.Copy
    > Range("A22").Select
    > ActiveSheet.Paste
    > (...)
    >
    > In particular, I can't see what is the goal of selecting first BA:BD, then
    > BC:BD, then BD:BF and finally BE:BZ...
    > Is it me that don't understand something or the code should be written
    > differently? (hint: it's still functionning correctly and it is not
    > deleting the data pasted in BA22 (lines 4-8 of the code)).
    >
    > Many thanks for any explanations!
    > Mark
    >
    >
    >
    >
     
    Otto Moehrbach, Mar 13, 2006
    #2
    1. Advertisements

  3. markx

    Guest Guest

    If you delete a selection of cells normally, if offers you the chance to
    shift the cells up or to the left or the entire row or column.

    The Shift:=xlLeft means it uses the Shift Cells Left option.

    HTH.

    "markx" wrote:

    > Hello,
    >
    > Could someone explain to me what is the meaning of the VBA expression:
    > Selection.Delete Shift:=xlToLeft
    >
    > Intuitively, I would suppose that it should delete the selection, but why
    > there is this "Shift" part coming after?
    >
    > The whole part of the code I try to analyze goes like this (and it looks
    > like a big garbage for me):
    >
    > (...)
    > Range("A3").Select
    > Range(Selection, Selection.End(xlToRight)).Select
    > Range(Selection, Selection.End(xlDown)).Select
    > Selection.Copy
    > Sheets("Confirmations").Select
    > Range("BA22").Select
    > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
    > _
    > False, Transpose:=False
    > Columns("BA:BD").Select
    > Application.CutCopyMode = False
    > Selection.Delete Shift:=xlToLeft
    > Columns("BC:BD").Select
    > Selection.Delete Shift:=xlToLeft
    > Columns("BD:BF").Select
    > Selection.Delete Shift:=xlToLeft
    > Columns("BE:BZ").Select
    > Selection.Delete Shift:=xlToLeft
    > Range("BA22:BD55").Select
    > Selection.Copy
    > Range("A22").Select
    > ActiveSheet.Paste
    > (...)
    >
    > In particular, I can't see what is the goal of selecting first BA:BD, then
    > BC:BD, then BD:BF and finally BE:BZ...
    > Is it me that don't understand something or the code should be written
    > differently? (hint: it's still functionning correctly and it is not deleting
    > the data pasted in BA22 (lines 4-8 of the code)).
    >
    > Many thanks for any explanations!
    > Mark
    >
    >
    >
    >
    >
     
    Guest, Mar 13, 2006
    #3
  4. markx

    markx Guest

    Thanks to you both!
    Now I understand much better this part of code...
    Have a nice day!

    "DaveO" <> wrote in message
    news:...
    > If you delete a selection of cells normally, if offers you the chance to
    > shift the cells up or to the left or the entire row or column.
    >
    > The Shift:=xlLeft means it uses the Shift Cells Left option.
    >
    > HTH.
    >
    > "markx" wrote:
    >
    >> Hello,
    >>
    >> Could someone explain to me what is the meaning of the VBA expression:
    >> Selection.Delete Shift:=xlToLeft
    >>
    >> Intuitively, I would suppose that it should delete the selection, but why
    >> there is this "Shift" part coming after?
    >>
    >> The whole part of the code I try to analyze goes like this (and it looks
    >> like a big garbage for me):
    >>
    >> (...)
    >> Range("A3").Select
    >> Range(Selection, Selection.End(xlToRight)).Select
    >> Range(Selection, Selection.End(xlDown)).Select
    >> Selection.Copy
    >> Sheets("Confirmations").Select
    >> Range("BA22").Select
    >> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
    >> SkipBlanks:=
    >> _
    >> False, Transpose:=False
    >> Columns("BA:BD").Select
    >> Application.CutCopyMode = False
    >> Selection.Delete Shift:=xlToLeft
    >> Columns("BC:BD").Select
    >> Selection.Delete Shift:=xlToLeft
    >> Columns("BD:BF").Select
    >> Selection.Delete Shift:=xlToLeft
    >> Columns("BE:BZ").Select
    >> Selection.Delete Shift:=xlToLeft
    >> Range("BA22:BD55").Select
    >> Selection.Copy
    >> Range("A22").Select
    >> ActiveSheet.Paste
    >> (...)
    >>
    >> In particular, I can't see what is the goal of selecting first BA:BD,
    >> then
    >> BC:BD, then BD:BF and finally BE:BZ...
    >> Is it me that don't understand something or the code should be written
    >> differently? (hint: it's still functionning correctly and it is not
    >> deleting
    >> the data pasted in BA22 (lines 4-8 of the code)).
    >>
    >> Many thanks for any explanations!
    >> Mark
    >>
    >>
    >>
    >>
    >>
     
    markx, Mar 13, 2006
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. MarcB

    Loop too slow deleteing xltoleft

    MarcB, Jun 8, 2004, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    377
    MarcB
    Jun 9, 2004
  2. Norman Jones

    Selection.End(xlToLeft).Select

    Norman Jones, Aug 12, 2005, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    2,507
    Norbert Jaeger
    Aug 12, 2005
  3. Guest
    Replies:
    2
    Views:
    5,390
    Chip Pearson
    Dec 28, 2005
  4. Excel VBA Selection.Delete Shift:=xlToLeft

    , Feb 14, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    1,352
    Dave Peterson
    Feb 14, 2006
  5. Uninvisible

    Copy Selection - Paste Selection - Delete Selection

    Uninvisible, Oct 23, 2007, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    594
    Uninvisible
    Oct 25, 2007
Loading...

Share This Page