Sorta fuzzy on the range object - when do I... 1

[WantsToLearn]

I'm pretty new to Excel VBA. Can someone help clear up the cobwebs on the range object and the specialcells object? When do I need to activate or select the range to work with it? I'm a little confused as you can tell from the question.

As an example say I define a range object to be the range B1:C10. Now, if I do rng.specialcells(onlyFormula) (Sorry, I don't know the exact syntax without looking it up), that will return a reference to formula cells within B1:C10 correct? Do I need a second range object to hold the special cells result?

How do I iterate the formula cells returned? Do I need to select or activate them first before I can do that?

I can see that the range object is a very powerful object but I'm having trouble my arms around it so that I can begin to work with it.

Thank you for your help!

 

[rmikesmith]

Hi!

You rarely need to select a range in order to work with it. In fact, this will slow your code's execution considerably. Taking your example, the following procedure will iterate through all the cells in B1:C10 that contain formulas. To see it's output, make sure you have the Immediate window displayed in the VBE.

Sub IterateSpecialCells()
Dim Rng As Range
Dim OneCell As Range

  Set Rng = Worksheets("Sheet1").Range("B1:C10").SpecialCells(xlCellTypeFormulas)
  
  For Each OneCell In Rng
    Debug.Print OneCell.Formula
  Next OneCell

End Sub

Here is another example of using a range object. This procedure copies & pastes the values of the formulas from the first example. Note, this works even if another worksheet is active.

Sub CopySpecialCellValues()
Dim Wks As Worksheet
Dim Rng As Range
Dim OneCell As Range

  Set Wks = Worksheets("Sheet1")
  Set Rng = Wks.Range("B1:C10").SpecialCells(xlCellTypeFormulas)
  
  Rng.Copy
  Wks.Range("E1").PasteSpecial xlPasteValues
  Application.CutCopyMode = False

End Sub

A very good way to undertand this further is to use the Object Browser and online Help. Select the Range object then explore the various Properties and Methods. Check out the examples given, also.


HTH
Mike

 

[SBendBuckeye]

Thanks, that was very helpful. One quick question and then it will have to wait until Monday.

In the first example you gave above, is it true that the range B1:C10 was never actually defined as its own separate range object? If I understand what you did, B1:C10 was just the boundary passed to the specialcells method and then the rng object itself contained maybe B4, B8, C2, C7, etc.

Thanks again for your help!

 

[rmikesmith]

SBendBuckeye,

Yes, you have characterized the first example correctly. I could have explicitly assigned a Range object variable to this range, however. Example:

Set RangeToCheck = Worksheets("Sheet1").Range("B1:C10")

The cells of this range containing formulas would now be obtained by the following:

Set Rng = RangeToCheck.SpecialCells(xlCellTypeFormulas)

Setting up a variable such as "RangeToCheck" is mostly done as a convenience; i.e. it allows the programmer to use a shorthand reference to an object.


Regards,
Mike