Loop from left to the right
 

With Worksheets("sheetA")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
For Each X In rng
'
Next X
End With

With above code i can loop through all rows of my data in column(A).
Say my data consists of unknown numbers of rows and columns and i want to loop from last cell of column(A) to the right most column which is unkown. I mean from left most to the right most column of last row with data.
How can i modify above code for that purpose ?
Thank you very much in advance.

Loop from left to the right
 

"rumkus" wrote:

With Worksheets("sheetA")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
For Each X In rng
'
Next X
End With

With above code i can loop through all rows of my data in column(A).
Say my data consists of unknown numbers of rows and columns and i want
to loop from last cell of column(A) to the right most column which is
unkown. I mean from left most to the right most column of last row with
data. How can i modify above code for that purpose ?

I'd do it like this:

'find the bottom of A:
ro = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1).End(xlUp).Row
'step through all cells in that row:
For col = 1 To Cells.SpecialCells(xlCellTypeLastCell).Column
Debug.Print Cells(ro, col).Value
Next

Adjust as you see fit.

--
- We have to compromise!
- No. Not even in the face of Armageddon. Never compromise.

Loop from left to the right
 

Dim rng As Range
Dim I As Integer
Dim X as range
I = 2
With Worksheets("sheet2")
Set rng = .Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft))
For Each X in rng
'do stuff
Next X
End With



Gord


On Mon, 12 Mar 2012 14:23:51 -0700 (PDT), wrote:

With Worksheets("sheetA")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
For Each X In rng
'
Next X
End With

With above code i can loop through all rows of my data in column(A).
Say my data consists of unknown numbers of rows and columns and i want to loop from last cell of column(A) to the right most column which is unkown. I mean from left most to the right most column of last row with data.
How can i modify above code for that purpose ?
Thank you very much in advance.

Loop from left to the right
 

One way...

Dim vData As Variant, i As Long, j As Long

'Get the data
vData = Sheets("sheetA").UsedRange
For i = LBound(vData) To UBound(vData)
For j = LBound(vData, 2) To UBound(vData, 2)
vData(i, j) = UCase$(vData(i, j)) 'do stuff
Next 'j
Next 'i

'Put the modified data back
Sheets("sheetA").Range("A1").Resize(i, j) = vData

...as iterating an array will always be faster than read/write each cell
in a range.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Loop from left to the right
 

Kinda missed the reading part.

Thought you wanted to go across from A2.

See Auric's post.


Gord

On Mon, 12 Mar 2012 15:33:27 -0700, Gord Dibben
wrote:

Dim rng As Range
Dim I As Integer
Dim X as range
I = 2
With Worksheets("sheet2")
Set rng = .Range(Cells(I, "A"), Cells(I, Columns.Count).End(xlToLeft))
For Each X in rng
'do stuff
Next X
End With



Gord


On Mon, 12 Mar 2012 14:23:51 -0700 (PDT), wrote:

With Worksheets("sheetA")
Set rng = .Range("a2", .Range("a" & Rows.Count).End(xlUp))
For Each X In rng
'
Next X
End With

With above code i can loop through all rows of my data in column(A).
Say my data consists of unknown numbers of rows and columns and i want to loop from last cell of column(A) to the right most column which is unkown. I mean from left most to the right most column of last row with data.
How can i modify above code for that purpose ?
Thank you very much in advance.

Loop from left to the right
 

And who says God hasn't left any angels on this world ?
Thank you so much.