Range defining problem

[Gromace]

Hello,

I have the following code in an Excel 2007 macro:

Range("M5:M18264").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _:=False, Transpose:=False

The problem is that I will not know the length of the range each time I run the macro. The cells contain a formula and I am trying to get rid of the formula and paste just the values. How can I define and select that range automatically then copy it and paste special values?

Any help would be greatly appreciated!

Thanks in advance,

Gromace

 

[Airfix9]

Assuming your range is continuous, how about this:

        Dim lr As Long
        lr = Range("M5").End(xlDown).Row
        With Range("M5:M" & lr)
            .Copy
            .PasteSpecial xlPasteValues
        End With

That should do ya.

 

[jmthompson]

What will define the variable length. Will the range always begin at the same cell (M5), but the number of cells changes, is the last cell of the range always the last row of your data?

 

[MickG]

Try:-

With Range(Range("M1"), Range("M" & rows.Count).End(xlUp))
.value = .value
End With

 

[jmthompson]

Or:

Dim lr As Long
lr = Range("M5").End(xlDown).Row
Range("M5:M" & lr).Value = Range("M5:M" & lr).Value

 

[Gromace]

Thank you so much for all of the replies! I used the first one, from Airfix9, because it was first and it worked. I did not test the suggestions from MickG and jmthompson, but I still appreciate your response and your time.

Speaking of time, I'd like to thank all of you at Mr.Excel. My problem was solved, most likely multiple times, by three different individuals, in less than 8 minutes! Fantastic!!!

What a great group of people!

Thanks again,

Gromace

 

[craig80]

Hi Guys,

Just wanted to say thank you, I hunted for something like this for hours.

Really appreciate it.

I used AirFix's option as it was first one I tested.

Craig