Solved

More efficient code using "with class" and .Cells, .Range, etc.? Excel, VBA

Posted on 2011-03-11
3
313 Views
Last Modified: 2012-05-11
I'm trying to find more efficient methods for iterating through multiple row values in multiple workbooks. I think this is best illustrated through some code:

with data
  If .Cells(.Range("custom_name").Row + i, .Range("custom_name").Column) > Date then
    do something here
  end if
end with

Open in new window


Basically...I'm looking for a way to be able to start my "with" statement with data.Range and then back track into .Cells

...or find a different way to call a specific cell without the above .Cells(.Range1.Row, .Range2.Row) syntax, which is currently making my code extremely dense.
0
Comment
Question by:keats88
3 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35112305
Assuming data is a variable defined as a range object and containing a range object then you are good to go.

Here are some additional notes on referencing ranges (and cells) you may find useful:

There are many ways to specify a range in Visual Basic. Below are some examples.

   [A1]
   [A1:B2]
   [A:A]
   [1:1]
   [RangeName]
   Range("A1")
   Range("A1:B2")
   Range("A:B")
   Range("1:2")
   Range(Cells(Row1, Column1), Cells(Row2, Column2))
   Range(RangeVariable, RangeVariable)
   Range("RangeName")
   Cells(Row, Column)
   Cells(Row, "ColumnLetter") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   RangeVariable.Range("C1:E1")
   RangeVariable.EntireRow.Cells(1, "Z") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   RangeVariable.EntireColumn.Cells(10, 1)
   Columns(1)
   Columns("A") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   Columns("A:B") (Important: see note at the end of this article about a bug in Excel 2003 and earlier)
   Rows(1)
   Rows("1:2")

When using a "ColumnLetter" parameter with the Cells or Columns property, the letter is translated into an absolute column number.

All of the above referencing methods except for the bracketed method ([A1]) can be used on any previously defined range object. When used in this manner, the row and column are treated as offsets into the range. They are also not limited to the parent range and thus can reference cells beyond the range's boundary. Below are some examples.

   [C1:E1].Cells(1, "D") equals [C1:E1].Cells(1, 4) or [F1]
   [C5].Columns("B") equals [C5].Columns(2) or [D5]
   [C5].Rows(4).Columns(2) equals [D8]

Note that the Cells method must be used when indexing into a range derived from the Rows or Columns collections, the EntireRow and EntireColumn objects, or when indexing into a range specified with square brackets:

   Columns("B")(23) will fail so use Columns("B").Cells(23)
   Rows(2)(1, 3) will fail so use Rows(2).Cells(1, 3)
   [B2].EntireRow(1, "C") will fail so use [B2].EntireRow.Cells(1, "C")
   [B10:B20](23) will fail so use [B10:B20].Cells(5)

If starting with a range that does not start in row 1 or column A and an absolute row or column is desired, use the EntireColumn or EntireRow method:

   [C5].EntireRow.Cells(1, "D") equals [D5]
   [C5].EntireRow.Columns("D") equals [D5]
   [C5].EntireColumn.Cells(10, 1) equals [C10]
   [C5].EntireColumn.Rows(10) equals [C10]

When passing range variables to the Range method the top left cell of the first parameter and the bottom right cell of the second parameter are used and the result is the smallest rectangular range encompasing both range parameters:

   Range([B2:C3], [D4:E5)] equates to B2:E5

Use commas to create unions:

   [A1,B1,C1:C5]
   Range("A1,B1,C1:C5")
   [RangeName1, RangeName2]

Note that the text passed to Range and in square brackets cannot be longer than 255 characters.

Use spaces to create intersections:

   [1:2 A:B] equates to A1:B2
   
To offset from a particular cell:

   [B2].Cells(2, 3) equates to [C4] and is the same as [B2].Offset(1, 2)

To double offset from a particular cell:

   [B2].Cells(2, 3)(4, 1) translates to [D6] and is the same as [B2].Offset(1, 2).Offset(3, 0)

To index into a range of cells starting from the top left cell and moving across each row from left to right and then down each row from top to bottom:

   [B2:D4].Cells(Index)
   [B2:D4].Cells(1) equates to [B2]
   [B2:D4].Cells(5) equates to [C3]

This technique works for any shape of range. Note that indexing continues down past the end of the specified range:

   [B2:D4].Cells(10) equates to [B5]

Top left cell of a range:

   Target(1, 1)

Bottom right cell of a range:

   Target(Target.Rows.Count, Target.Columns.Count)

Nth row or column of a range:

   Target.Rows(N)
   Target.Columns(N)

Working with all rows or columns in a range:

   For Each Row In Target.Rows
      Row(1, 1)
      Row(1, 2)
   Next Row

   For Each Column In Target.Columns
      Column(1, 1)
      Column(2, 1)
   Next Column

Important: Note that the Columns and Cells collections fail when passed a column letter and the active sheet is a chart or there is no visible workbook. This bug was resolved in Excel 2007. The workaround is to use the Range method. The problem occurs even if a specific parent expression is used which fully qualifies a worksheet. The following examples will fail when a chart sheet is active or there is no visible workbook:

   Set MyRange = ThisWorkbook.Worksheets(1).Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).Cells(1, "A")
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Cells(1, "A")

These, however, will work:

   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Cells(1, "A")
   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Range("A1:C10").Columns("A")
   Set MyRange = ThisWorkbook.Worksheets(1).[A1:IV65536].Range("A1:C10").Cells(1, "A")

As will these:

   Set MyRange = ThisWorkbook.Worksheets(1).Columns(1)
   Set MyRange = ThisWorkbook.Worksheets(1).Cells(1, 1)
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Columns(1)
   Set MyRange = ThisWorkbook.Worksheets(1).Range("A1:C10").Cells(1, 1)

Kevin
0
 
LVL 29

Expert Comment

by:gowflow
ID: 35117352
One way of iterating thru a range without having to go thru heavy repettitive syntax is to do something like this

For each Cell in Data.Cells(Data.Range("custom_name").Row + i, Data.Range("custom_name").Column)

if Cell > Date then
 do something
Next Cell

gowflow
0
 

Author Closing Comment

by:keats88
ID: 35158954
Great information, thank you!
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question