DELETE FORMULES - Page 2
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 23

Thread: DELETE FORMULES

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    On 2002-03-26 12:30, Barrie Davidson wrote:
    I'm not totally sure of your requirements. If I understand correctly, the following code will take the values in column B and copy them to first blank column from column B (leaving the original formulas in column B).


    No it is just the other way: take the formules in column B and copy them to column C (leaving the original values in column B)
    The following day : take the formules in column C and copy them to column D (leaving the original values in column C) and so on each day a further column.
    Thats what I mean.

    Range("B1:" & Range("B65536").End(xlUp).Address).Copy
    Range(Range("IV1").End(xlToLeft).Offset(0, 1) _
    .Address).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False



    Is this what you need?

    _________________

    Barrie Davidson
    My Excel Web Page

    [ This Message was edited by: Barrie Davidson on 2002-03-26 12:31 ]

  2. #12
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Okay, how about this then:
    Code:
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
        Range("IV1").End(xlToLeft).End(xlDown). _
        Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Are we there yet?
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  3. #13
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 12:59, Barrie Davidson wrote:
    Okay, how about this then:
    Code:
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
        Range("IV1").End(xlToLeft).End(xlDown). _
        Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Are we there yet?
    What do you mean with: Range("IV1") ?
    Thanks.

  4. #14
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 14:43, verluc wrote:
    On 2002-03-26 12:59, Barrie Davidson wrote:
    Okay, how about this then:
    Code:
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
        Range("IV1").End(xlToLeft).End(xlDown). _
        Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Are we there yet?
    What do you mean with: Range("IV1") ?
    Thanks.
    Range("IV1") is the last cell in row 1. I use that to get the last column to the right (I wasn't sure if you had anything in cell A1, so I assumed you did not).

    Regards,
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 15:09, Barrie Davidson wrote:
    On 2002-03-26 14:43, verluc wrote:
    On 2002-03-26 12:59, Barrie Davidson wrote:
    Okay, how about this then:
    Code:
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
        Range("IV1").End(xlToLeft).End(xlDown). _
        Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Are we there yet?
    What do you mean with: Range("IV1") ?
    Thanks.
    Range("IV1") is the last cell in row 1. I use that to get the last column to the right (I wasn't sure if you had anything in cell A1, so I assumed you did not).

    Regards,
    Sorry,but this macro dont work.I receive an error on the line Range("IV1")
    In column A I have the symbols of the stocks.In column B are the quotes of the stocks.
    Thanks for your answer.

  6. #16
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry,but this macro dont work.I receive an error on the line Range("IV1")
    In column A I have the symbols of the stocks.In column B are the quotes of the stocks.
    Thanks for your answer.
    I'm curious, which line gave the error (three lines begin with Range("IV1"))? It worked fine for me when I tested it.
    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  7. #17
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 15:29, Barrie Davidson wrote:
    Sorry,but this macro dont work.I receive an error on the line Range("IV1")
    In column A I have the symbols of the stocks.In column B are the quotes of the stocks.
    Thanks for your answer.
    I'm curious, which line gave the error (three lines begin with Range("IV1"))? It worked fine for me when I tested it.
    Here is the complete macro,who is in my program:Sub Copy()
    Sheets("Portefeuille").Select
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
    Range("IV1").End(xlToLeft).End(xlDown). _
    Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub

    It is on the first Range("IV1") that I receive the error.
    Many thanks

  8. #18
    MrExcel MVP Barrie Davidson's Avatar
    Join Date
    Feb 2002
    Location
    Winnipeg
    Posts
    2,330
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here is the complete macro,who is in my program:Sub Copy()
    Sheets("Portefeuille").Select
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
    Range("IV1").End(xlToLeft).End(xlDown). _
    Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub

    It is on the first Range("IV1") that I receive the error.
    Many thanks
    Do you have anything in cell B1? I'm betting not; if you only have something in cell A1, the macro is trying to go to the left of cell A1.

    Range("IV1").End(xlToLeft).Offset(0, -1)

    This code will go to the column left of the rightmost column of data in row 1. For example, if your rightmost cell is C1, it will go to cell B1. As you can see, you will get an error if your rightmost cell (in row 1) is A1.

    Am I correct?



    Barrie Davidson

    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  9. #19
    Board Regular
    Join Date
    Mar 2002
    Posts
    1,451
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-26 15:43, Barrie Davidson wrote:
    Here is the complete macro,who is in my program:Sub Copy()
    Sheets("Portefeuille").Select
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
    Range("IV1").End(xlToLeft).End(xlDown). _
    Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub

    It is on the first Range("IV1") that I receive the error.
    Many thanks
    Do you have anything in cell B1? I'm betting not; if you only have something in cell A1, the macro is trying to go to the left of cell A1.

    Range("IV1").End(xlToLeft).Offset(0, -1)

    This code will go to the column left of the rightmost column of data in row 1. For example, if your rightmost cell is C1, it will go to cell B1. As you can see, you will get an error if your rightmost cell (in row 1) is A1.

    Am I correct?

    Great, It works.Many,Many thanks.


  10. #20

    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-03-26 15:52, verluc wrote:
    On 2002-03-26 15:43, Barrie Davidson wrote:
    Here is the complete macro,who is in my program:Sub Copy()
    Sheets("Portefeuille").Select
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert
    Range(Range("IV1").End(xlToLeft).Address, _
    Range("IV1").End(xlToLeft).End(xlDown). _
    Address).Copy
    Range("IV1").End(xlToLeft).Offset(0, -1).PasteSpecial _
    Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    End Sub

    It is on the first Range("IV1") that I receive the error.
    Many thanks
    Do you have anything in cell B1? I'm betting not; if you only have something in cell A1, the macro is trying to go to the left of cell A1.

    Range("IV1").End(xlToLeft).Offset(0, -1)

    This code will go to the column left of the rightmost column of data in row 1. For example, if your rightmost cell is C1, it will go to cell B1. As you can see, you will get an error if your rightmost cell (in row 1) is A1.

    Am I correct?

    Great, It works.Many,Many thanks.


    Re the line that reads :-
    Range("IV1").End(xlToLeft).Offset(0, -1).EntireColumn.Insert

    Should this not be :-
    Range("IV1").End(xlToLeft)).EntireColumn.Insert


    The complete code could be simplified to :-

    With Sheets("Portefeuille").Range("IV1").End(xlToLeft).EntireColumn
    .Insert
    .Offset(0, -1) = .Value
    End With








User Tag List

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