Dynamic selection of rows based on cell value?

[TheWennerWoman]

I have some data and need a macro that I can return to that will pick up where it left off, if that makes sense.

I have several thousand rows of data starting at row 2, I wish to select 100 records one at a time (column K). When those records have been selected, column L is stamped with "Done".

So for the first selection, that's easy, my code would simply select the values in K2:K101 (one at a time), do whatever I want it to do and then stamp column L with "Done". If I return to the macro tomorrow and run it, it would be smart enough to start at row 102, select another 100 records, do whatever I want it to and then stamp those rows with "Done".

And so on.

Hope someone can help and I hope I have made sense

 

[footoo]

Cells(Cells(Rows.Count, "L").End(3)(2).Row, "K").Resize(100).Select

 

[GWteB]

As I understand it, there are things the macro needs to do and things you plan to do manually. It's not clear to me what exactly the macro should do.

When those records have been selected

While in 99.9% of the cases, no selection of a worksheet range is required to perform automated manipulations in that range, the code below does select a range in column K, based on whether or not column L contains a value or text.

Sub TheWennerWoman()

    Dim ws As Worksheet, rng As Range
    Set ws = Application.ActiveSheet
    ws.Range("L" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row).Offset(1, -1).Resize(100, 1).Select
End Sub

 

[TheWennerWoman]

So, column K contains a list of URLs. The first time I run the macro, it will start at cell K2 and, using a For Next loop, will open the url and save it locally. Stamp cell L2 with "Done". Increase the loop by 1, rinse and repeat 100 times.

When I come back tomorrow and fire up the macro, it will start from K102 (because it can see that L101 is the last cell stamped with "Done").

Hope that explains it a little better, apologies for not being clear first time around

 

[GWteB]

Do you have already any code at all regarding your query?

The "when I come back tomorrow" part is pretty clear, but how about ...

will open the url and save it locally

- what kind of url would that be?
- what happens when the url is opened?
- what has to be saved?
- do you want the url to be copied to another cell?
- has something to be saved on disk?
Please elaborate on the above.

 

[TheWennerWoman]

This is my feeble attempt so far, very inefficient I am sure

Option Explicit

Sub Open_Url()

Dim ChromeLocation As String
Dim MyURL As String
Dim i As Integer
Dim b As Integer
Dim ws1 As Worksheet
Dim lastRow As Long

Set ws1 = Sheets("data")
Range("K1").Select
b = 0

lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

ChromeLocation = "C:\Program Files\Google\Chrome\Application\chrome.exe"

For i = 2 To lastRow
If Cells(i, 12).Value <> "Done" Then
    MyURL = ws1.Cells(i, 11)
    Shell (ChromeLocation & " -url -newtab " & MyURL), vbNormalFocus
    Cells(i, 12).Value = "Done"
    b = b + 1
    If b = 100 Then Exit Sub
End If
Next i
Exit Sub
End Sub

If anyone can improve it, please feel free

 

[footoo]

Sub Open_Url()

Dim rng As Range, cel As Range
Dim ChromeLocation As String

Sheets("data").Select

Set rng = Cells(Cells(Rows.Count, "L").End(3)(2).Row, "K").Resize(100)

ChromeLocation = "C:\Program Files\Google\Chrome\Application\chrome.exe"

For Each cel In rng
    Shell (ChromeLocation & " -url -newtab " & cel), vbNormalFocus
    cel(1, 2) = "Done"
Next
End Sub