1

I'm new to VBA, and I'd love some help.

I have a spreadsheet that has a range that will always have 10 columns, but could have X amount of rows. I'm trying to write a VBA script that will iterate over this range and extract the values out of each cell.

For example, if I have 2 rows I need A1, B1, C1..J1 and then I need A2, B2, C2...J2. The next time I could have 5 rows and then another time 3.

How do I setup my loop to make this work?

Mark
  • 297
  • 5
  • 15
  • To determine the last used row: http://stackoverflow.com/a/10642588/829571 – assylias Jun 26 '12 at 16:56
  • YOu can also determine the last row by using `UsedRange.Rows.Count` if it is not a column specific row count. – Scott Holtzman Jun 26 '12 at 17:03
  • How about assigning `Range("A1").CurrentRegion.Value` to a Variant variable and then iterating through that? Is that possible? – JimmyPena Jun 26 '12 at 17:50
  • @Mark: I will never recommend using `UsedRange` as they are highly unreliable in finding the last row in a worksheet. Use this method. http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920 – Siddharth Rout Jun 26 '12 at 18:42

2 Answers2

0

something like

Dim lastRow as long

lastRow = UsedRange.Rows.Count     'or some other way of determining the last row used

    for i = 1 to lastRow    'loop through all used rows

    'ActiveSheet.Cells(i, 1).value    'column A
    'ActiveSheet.Cells(i, 2).value    'column B
    'ActiveSheet.Cells(i, 3).value    'column C

    next i
Sam
  • 7,245
  • 3
  • 25
  • 37
  • Excellent! I used this with the "UsedRange.Rows.Count" suggested above. – Mark Jun 26 '12 at 18:22
  • I'm glad you found it useful. As already said by Siddharth, UsedRange.Rows.Count is not a good way of finding the last row of data. – Sam Jun 27 '12 at 17:55
0

You could also use a dynamic named range and loop through that. See this link or google for better examples. Dynamic name ranges are powerful, especially for charts.

For your example you would set the name range reference to;

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),10) '10 is the width and will go to column J

assuming that column A will have the true max row of the table.

Then;

Dim arr() As Variant

 arr = Range("YourRangeName")

  For x = 1 To UBound(arr,1) 'Finds the max number of rows in the array, UBound(arr,2) finds the total columns.
    'Do some code here where x steps through the array arr
    '  = arr(x, 1) 'column a
    '  = arr(x,2)  'column b 
    '  = arr(x,3) ....     
  Next x

It's almost always better/faster to process as much as you can in code, i.e. assigning a range in Excel to an Array then loop through the array rather than referencing cells (especially in a loops).

Rory
  • 396
  • 1
  • 5