DELETE FORMULES

[verluc]

How can I whrite a macro that delete all the formules in column B (+/- 7000 lines)without deleted the values in the column?
Many thanks.

 

[verluc]

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).<pre>
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</pre>


Is this what you need?

_________________

Barrie Davidson
My Excel Web Page
This message was edited by Barrie Davidson on 2002-03-26 12:31

 

[Barrie Davidson]

Okay, how about this then:

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?

 

[verluc]

On 2002-03-26 12:59, Barrie Davidson wrote:
Okay, how about this then:

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.

 

[Barrie Davidson]

On 2002-03-26 14:43, verluc wrote:

On 2002-03-26 12:59, Barrie Davidson wrote:
Okay, how about this then:

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,

 

[verluc]

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:

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.

 

[Barrie Davidson]

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.

 

[verluc]

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

 

[Barrie Davidson]

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?

 

[verluc]

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.

 

[Amds]

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