Dynamic Range Selection 1,000 rows at a time

  • Thread starter Thread starter Kirk P.
  • Start date Start date
K

Kirk P.

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
 
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
 
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.
 
Like this

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

Selection.Copy Destination:=Sheets("Somesheet").Range("A1")
Next
End Sub

Mike
 
I see this transfers the data from one sheet to another, which isn't entirely
helpful. What I'm really trying to do is accomodate an Oracle PL/SQL query,
which can only handle 1,000 items at a time in its IN function. I'm using
Excel to identify those 1,000 items in which to run the query.

I've got this long list of items in Excel, and what I'm really trying to do
is to automate the selection of each group of 1,000 items all the way down
the list. My current process is to manually go through the list, select the
first 1,000 items, copy them into my SQL statement, run the query, manually
select the next 1,000 rows, copy them into my SQL statement, run the query,
and so on. For a list of nearly 128,000 items I have to manually select my
1,000 item block 128 times.

What I'm looking for is to kick off a macro that selects the first 1,000
items, stops and allows me to copy/paste those items into my SQL, then I need
some way to continue the macro so it selects the NEXT 1,000 items, then
stopping and allowing me to copy/paste etc, etc all the way down the list.
 
Did you try the macro I suggested?

Kirk P. said:
I see this transfers the data from one sheet to another, which isn't entirely
helpful. What I'm really trying to do is accomodate an Oracle PL/SQL query,
which can only handle 1,000 items at a time in its IN function. I'm using
Excel to identify those 1,000 items in which to run the query.

I've got this long list of items in Excel, and what I'm really trying to do
is to automate the selection of each group of 1,000 items all the way down
the list. My current process is to manually go through the list, select the
first 1,000 items, copy them into my SQL statement, run the query, manually
select the next 1,000 rows, copy them into my SQL statement, run the query,
and so on. For a list of nearly 128,000 items I have to manually select my
1,000 item block 128 times.

What I'm looking for is to kick off a macro that selects the first 1,000
items, stops and allows me to copy/paste those items into my SQL, then I need
some way to continue the macro so it selects the NEXT 1,000 items, then
stopping and allowing me to copy/paste etc, etc all the way down the list.
Click to expand...
 
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?
 
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.
 
Back
Top