Loop Through Rows faster

[rollin]

Hi All,

I need help with my code to make it loop faster. I have this code in my Format Report routine. My report runs and then the Formatting is done at the end.

It inserts a column, HLINK as the first field and then loops from first row to the last row containing data to insert a hyperlink on the first column. Since the number of rows maybe from 1000 or more,
it takes a while to loop. Is there a way to make this fast?

    Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Value = " HLINK"   
    hlink = Parameters.Range("UrlServerAddress").Value
    With ActiveSheet
    col = FindCol("HLINK", 1, .Name)
    LastRow = .Range("A1").CurrentRegion.Rows.Count
    If col > 0 Then
        For rw = 2 To LastRow
        Cells(rw, col).Select       
            'Make a call to API and pass parameters to HLINK.
            ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http://" & hlink & "/cgi /PControl.cgi?tool= HLINK&sc=" & str & "&ph=" & Range("K" & rw).Value _
            & "&stme=" & Range("Y" & rw).Value & "&etme=" & etme, TextToDisplay:="HLINK"
            
            Range(.Cells(2, col), .Cells(LastRow, col)).Value = " HLINK"
            Range(.Cells(2, col), .Cells(LastRow, col)).Font.Underline = True
            Range(.Cells(2, col), .Cells(LastRow, col)).Font.Color = vbBlue
        
        Next rw
    End If
    End With

Thanks,
Rollin

[Norie]

Rollin

Couldn't you use the HYPERLINK function for the hyperlink?

If you could then you wouldn't need to loop.

[rollin]

Hi,
Thanks. Can you please explain how my code would work with the HYPERLINK function?

an example would help.

[Norie]

Not sure about your exact URL but it might look something like this.

             strURL = "http://" & hlink & "/cgi /PControl.cgi?tool= HLINK&sc=" & str & "&ph=""&K2&""" & _
             "&stme=""&Y2&""&etme=" & etme
            
            With Range(Cells(2, col), Cells(lastrow, col))
                .Formula = "=HYPERLINK(""" & strURL & """,""HLINK"")"
                .Font.Underline = True
                .Font.Color = vbBlue
            End With

Note, you might not need the font formatting part.

[EssoExplJoe]

Adding "Application.ScreenUpdating = False" before the loop starts might help. Also setting the worksheet recalculation to False might help.