What is a Range

[AlexanderBB]

For example, if I have Dat= Range("O59:CL59") what have I got ?

I cannot see it in a Watch, nor print Len(Dat).
What can I do with it ?

Can I put it onto another sheet? And could it carry across any attributes (font, back color etc) as well as values ?

 

[Michael M]

A Range Object represents a cell or multiple cells in your Excel worksheet.
You can't see, print, copy, etc until you advise it to do so.

eg,

Dat.copy

will do just that.

Basically, Dat is now sitting there waiting for it's next instruction

 

[Fazza]

suggest you google. Such as https://www.excel-easy.com/vba/range-object.html


best practice is to dim range variables such as : dim Dat as excel.range

then to assign to Dat, something ilke : Set Dat = range("O59:CL59")

for objects "Set" is used. Like : Set wks = activesheet
for simple things like integers, "Set" is not used, rather "Let" which is normally omited as it is not essential
so for integers : let i = 1
or normally : i = 1

What do you want to do?

 

[AlexanderBB]

Thanks for the replies.
I don't get "Dat.Copy". Should there be intellisense?
Do I need to Dim Dat as Range? I had Dim Dat as variant (my thinking being variant was anything?)
From Fazza's link there was Range("C3:C4").Value = Range("A1:A2").Value which was useful. I tried it and it copied the values, but not the formatting.
Is is possible to work with ALL attributes, or do they need to be done individually?

 

[MARK858]

On the dimming as Variant part read the link below...

http://www.cpearson.com/excel/DeclaringVariables.aspx

PasteSpecial Paste:=xlPasteAll and Copy Destination:= both use all "attributes".

 

[AlexanderBB]

Thanks Mark, read the link.
But not sure how to apply your example. Could you show how it might look (with a range) and in a macro ?

 

[My Aswer Is This]

Sheets(1).Range("A1").value="You"

Puts the word You in Range("A1")

 

[MARK858]

Thanks Mark, read the link.
But not sure how to apply your example. Could you show how it might look (with a range) and in a macro ?

PasteSpecial Paste:=xlPasteAll and Copy Destination:= both use all "attributes".

Sub PasteAllexample()
    Range("A4").Copy
    Range("D4").PasteSpecial Paste:=xlPasteAll
End Sub

Sub ShorthandPasteAllexample()
    Range("A4").Copy
    Range("D4").PasteSpecial
End Sub

Sub Destinationexample()
    Range("A4").Copy Destination:=Range("D4")
End Sub

Sub ShorthandDestinationexample()
    Range("A4").Copy Range("D4")
End Sub

 

[AlexanderBB]

Thanks Mark, having no trouble now pasting & copying Ranges but some other aspects are a bit frustrating.

I want to compare two Ranges (in two Worksheets). I am putting these test into Sheet1 module.

If Worksheets("Sheet2").Range("O2:AA2").value <> Range("O2:AA2").value then

I tried with and without .Value. Neither worked

I also found Dim as Range isn't straightforward either

Dim g as Range
g= Range("O2:CL2")

also gives as error (with or without .Value)

Unless you Dim g as Variant.. but then maybe you haven't got a range ?

Dim g as Variant
Dim h as variant
g = Worksheets("Sheet2").Range("O2:AA2")
h= Range("O2:AA2")
if g <> h

Gave an error on the last line.

If there is some way to compare 2 ranges, what is it ? And would it include colours or fonts as well as values ?
Thanks.

 

[MARK858]

You need to loop though a range consisting of multiple cells for a comparison either on the sheet or in an array.

I also found Dim as Range isn't straightforward either
Dim g as Range
g= Range("O2:CL2")

A Range is an object so you you need the Set keyword...

Dim g as Range
Set g= Range("O2:CL2")