1

For example, if the data in one worksheet looks like: enter image description here

UsedRange.Columns.Count is 6 which is the maximum column count for all rows. Even if I iterate using

For each row in UsedRange.Rows
       For each cell in row.Cells
           ...
       Next cell
Next row

It's still counting 6 for each row.

Oxdeadbeef
  • 1,033
  • 2
  • 11
  • 26
  • I want to iterate 6 times for Row 1 and 3 times for Row 2, etc. You see what I mean? – Oxdeadbeef Mar 23 '12 at 17:48
  • why do you want that? you can test for empty cell in your iteration in vba or you can use a `=count(1:1)` / `=counta(1:1)` formula for each row... – Aprillion Mar 23 '12 at 17:56
  • got it. thanks. I thought for each row there's a similar UsedColumn functionality that can wrap the empty test. – Oxdeadbeef Mar 23 '12 at 18:22

2 Answers2

3

Just exit your cell loop if the cell is Empty.

For Each Row In UsedRange.Rows
   For Each cell In Row.Cells
       If IsEmpty(cell) Then
            Exit For
       End If

       'Do what you want here...

   Next cell
Next Row
Francis P
  • 13,377
  • 3
  • 27
  • 51
  • The "If cell is empty then" line has "Runtime error '424' Object required." Changing it to "If IsEmpty(cell) then" works. Thanks! – Oxdeadbeef Mar 23 '12 at 18:20
0

UsedRange will return a jointed range. In your case, a small test:

Sub test()
Debug.Print UsedRange.Address
End Sub

prints $A$1:$F$4

So, you'd better check if there is any value in your cell before you execute your code.

See this SO thread: How to check if a cell is empty? to do so.

Community
  • 1
  • 1
JMax
  • 26,109
  • 12
  • 69
  • 88