Selecting range with loop in VBasic

[SeanF74]

I have a list of 10,000 lines of data starting from row 2 in Excel 2007. I
would like to select a range of cells from each line individually (columns C
through L) for each of the 10k lines and sort horizontally. I am trying to
use a do loop to do this, but I cannot set the range correctly with a
variable (say c) to create a dynamic range. Here is a simplified view of
what I have:

c = 2
Do While c < 15000
Range("C2:L2").Select
c = c +1
Loop

What syntax do I use in the Range.Select argument using the c variable to
move down a line each time I run through the Do Loop?

Thanks,

 

[Dave Peterson]

There are lots of different ways to get that range to sort.

One way:

Option Explicit
Sub testme()

Dim iRow As Long
Dim RngToSort As Range

For iRow = 2 To 10000
Set RngToSort = Worksheets("sheet1").Cells(iRow, "A").Range("c1:L1")

With RngToSort
.Sort Key1:=.Cells(1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
End With
Next iRow
End Sub

I could have used:
Set RngToSort = Worksheets("sheet1").Cells(iRow, "A").offset(0,2).resize(1,10)
Starting with the cell in column A, shift (offset) 2 columns to get to column C
and then resize to 1 row by 10 columns.

or just start with column C and avoid the .offset.
Set RngToSort = Worksheets("sheet1").Cells(iRow, "c").resize(1,10)

or

with worksheets(Sheet1")
set rngtosort = .range(.cells(irow,"C"),.cells(irow,"L"))
end with

with rngtosort ....

 

[michdenis]

Hi,

One more variations on this theme :

'----------------------------------
Sub test()
Dim LastRow As Long
Dim Rg As Range, R As Range

With Worksheets("Sheet1") 'Adapte name sheet
LastRow = .Range("C:H").Find(What:="*", _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Set Rg = .Range("C2:H" & LastRow)
End With
For Each R In Rg.Rows
MySort R
Next
End Sub
'----------------------------------
Sub MySort(R As Range)
With R
.Sort Key1:=R(1), Order1:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlLeftToRight
End With
End Sub
'----------------------------------



"SeanF74" <[email protected]> a écrit dans le message de groupe de
discussion : (e-mail address removed)...
I have a list of 10,000 lines of data starting from row 2 in Excel 2007. I
would like to select a range of cells from each line individually (columns C
through L) for each of the 10k lines and sort horizontally. I am trying to
use a do loop to do this, but I cannot set the range correctly with a
variable (say c) to create a dynamic range. Here is a simplified view of
what I have:

c = 2
Do While c < 15000
Range("C2:L2").Select
c = c +1
Loop

What syntax do I use in the Range.Select argument using the c variable to
move down a line each time I run through the Do Loop?

Thanks,