Use area property to set rows

Hi all,

This code sets an area based on columns, with groups of rows seperated by a blank row. it works well.

Set myAreas = Sheets(1).Columns("a:f").SpecialCells(2, 1).Areas

ArrayedAreaAddress = myareas(1).address

I'm looking for the method to set myAreas so that I can set the myareas by individual rows within the columns

e.g.

Set myAreas = Sheets(1).Columns("a:f").Rows.Areas

This doesn't work.

Would anybody know how this is done

Cheers

Areas is a property of a Range object which returns a collection that represents all the ranges in a multiple-area selection. The Rows Collection does not have an Areas property.
What are you looking for? A collection of Rows in a specified range of columns?


Please read the manual: http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

Thank you for your response.

Yes, I am looking for a collection of rows in a specified range of columns. I'm looking for a method that does (in principle) the same thing as the describe specialcells method, except instead of the area being defined by blank rows, i need the area defined by individual rows regardless of what's in the row.

Can you show such a method ?

Sheets(1).Range("a:f").Rows will return the collection you are looking for but it will be more than 1 million members...


Please read the manual: http://msdn.microsoft.com/en-us/libr...ffice.15).aspx

That property is good for feeding into an array, however, the problem is that I need the areas property. I need to compare a row range from one sheet against a row range from another sheet.

After a number of hours looking for this function, I have found that you can evaluate one area against another area.

So I need a row range ("A1:S1") defined as an area that I can evaluate against another row range (from another sheet)

It's a bit hard to understand how to help I'm afraid. What do you mean exactly by "evaluate one area against another?"


Sent from my iPhone using Tapatalk

This example may show what I'm trying to acheive, i.e everycell in RangeOne must match every cell in RangeTwo , except that this method doesn't work, but should at least demonstrate it.

For i = 1 to 100
   RangeOne = Worksheet("SheetOne").Range("A" & i & ":S" & i)
   For j = 1 to 100
      RangeTwo = Worksheet("SheetTwo").Range("A" & j & ":S" & j)
      If RangeTwo = RangeOne then
         'do stuff with RangeOne
      End if
   Next J
Next i

I really didnt want to loop through each cell in a row if there's a better way.