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.

"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.

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.

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.

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

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