I have a list of over 127,000 transaction_id's in Excel. I'm looking for a
way to automate the selection of the first 1,000 items in the list, then the
next 1,000 and so on until it reaches the bottom.

Help?

One way:

Public Sub SelectLotsOfRows()
Const cnRowsToSelect As Long = 1000
Static nStart As Long
Static nMax As Long

With Sheets("Sheet1")
If ActiveSheet.Name = .Name Then
If nMax = 0 Then
nStart = 1
nMax = .Rows.Count - cnRowsToSelect
Else
nStart = nStart + cnRowsToSelect
If (nStart .UsedRange.SpecialCells(xlLastCell).Row) Or _
(nStart nMax) Then nStart = 1
End If
.Cells(nStart, 1).Resize(cnRowsToSelect).EntireRow.Select
End If
End With
End Sub

In article ,
Kirk P. wrote:

I have a list of over 127,000 transaction_id's in Excel. I'm looking for a
way to automate the selection of the first 1,000 items in the list, then the
next 1,000 and so on until it reaches the bottom.

Help?

This does come very close. How would I modify this to find, for each 1,000
row block, the range of cells associated with the selected column in each
1,000 row block?

"JE McGimpsey" wrote:

One way:

Public Sub SelectLotsOfRows()
Const cnRowsToSelect As Long = 1000
Static nStart As Long
Static nMax As Long

With Sheets("Sheet1")
If ActiveSheet.Name = .Name Then
If nMax = 0 Then
nStart = 1
nMax = .Rows.Count - cnRowsToSelect
Else
nStart = nStart + cnRowsToSelect
If (nStart .UsedRange.SpecialCells(xlLastCell).Row) Or _
(nStart nMax) Then nStart = 1
End If
.Cells(nStart, 1).Resize(cnRowsToSelect).EntireRow.Select
End If
End With
End Sub

In article ,
Kirk P. wrote:

I have a list of over 127,000 transaction_id's in Excel. I'm looking for a
way to automate the selection of the first 1,000 items in the list, then the
next 1,000 and so on until it reaches the bottom.

Help?

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow Step 1000
Rows(i & ":" & WorksheetFunction.Min(i + 999, lastrow)).Select

'Do Something

Next
End Sub

Mike


"Kirk P." wrote:

I have a list of over 127,000 transaction_id's in Excel. I'm looking for a
way to automate the selection of the first 1,000 items in the list, then the
next 1,000 and so on until it reaches the bottom.

Help?

It appears to loop through all 128,000 rows, but in your 'Do Something'
portion of your code, how do I get it to stop at the first 1,000 rows, allow
me to copy the selected range, then move to the next 1,000 rows, allow me to
copy the selected range, etc, etc.

"Mike H" wrote:

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To lastrow Step 1000
Rows(i & ":" & WorksheetFunction.Min(i + 999, lastrow)).Select

'Do Something

Next
End Sub

Mike


"Kirk P." wrote:

I have a list of over 127,000 transaction_id's in Excel. I'm looking for a
way to automate the selection of the first 1,000 items in the list, then the
next 1,000 and so on until it reaches the bottom.

Help?

Hi Kirk,

You can handle this with mutliple queries to your database.

The syntax is database dependent.

In oracle you'd have

select * from mytable where rownum 1000 and rownum < 1000;

If you're using DB2 let me know - I'll get the exact syntax.