current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I was lucky enough to get a response from someone on a previous question I have asked. See here.

Only thing I am struggling with is creating a loop that will end the macro upon reaching a blank cell in Column B. I have been trying to read other code but nothing was really clicking for me (if, than loops, End With Loops, etc). This is the last step to my macro and I would love any resources on how to create loops and/or a solution to this issue for me. Thanks as always. The people on this site are beyond helpful!

Sub Redirect()
Dim IE As Object
Dim doc As Object

Set IE = CreateObject("InternetExplorer.Application")

With IE
    .Visible = True
    .Navigate "https://...."

    Do Until .ReadyState = 4: DoEvents: Loop

    With .Document.forms("digiSHOP")
        .elements("OldUrl").Value = Range("A2")
        .elements("NewUrl").Value = Range("B2")
        .submit
    End With

    Do Until .ReadyState = 4: DoEvents: Loop
    Do While .Busy: DoEvents: Loop

End With
End Sub
share|improve this question

1 Answer 1

up vote 3 down vote accepted

While you might actually want to loop until you get to a blank row, most people find it more useful to loop until the last populated row in a particular column, which works even if you have a few (or many) blanks in the column.

Note that you may not even need a loop. Using .autofilter or .find are much more efficient. (comparable to using a where clause in a SQL query instead of a cursor)

Having said all that, if you do need the loop, the best approach is to declare a few objects including the worksheet and range so that you can execute the loop without selecting or activating anything in your code. As any Excel VBA developer worth his or her salt will tell you, selecting and activating should always be avoided.

You can use the following as a template:

Sub LoopUntilLastRow()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim lastRow As Long
    Dim myColumn As String

    Set ws = ThisWorkbook.Sheets("Sheet1")
    myColumn = "A"
    lastRow = ws.Range(myColumn & ws.Rows.Count).End(xlUp).Row
    If lastRow = 1 Then
        MsgBox "Column " & UCase(myColumn) & " is empty."
        Exit Sub
    End If

    Set rng = ws.Range(myColumn & "1:" & myColumn & lastRow)
    For Each cell In rng
        ' do something
        cell.Interior.Color = vbYellow
    Next cell
End Sub
share|improve this answer
    
Awesome! Thank you Head of Catering! I will break your code down and read up on .autofilter and .find. I am not familiar with those...yet :) Thanks again. –  user2300923 Jun 1 '13 at 18:44
    
You're very welcome. I'm glad that worked for you. –  Jon Crowell Jun 1 '13 at 19:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.