I am writing a VBA script in Excel for processing only visible row after an autofilter.

In this script, I would like to access data from a website after the auotfilter since I would like to reduce the internet traffic.

However, I find that the following sample scipts cannot work properly when there is a lot of distinct group of the filtered worksheet. The rng2.address cannot hold the whole string when it is greater than 256. As a result, some rows will not be executed

Would any expert please help to solve the problem.

Best Regards,

Dennis


My testing VBA script for concept proof is as below for your reference.

Sub Update_quote()
'by Tom Ogilvy
Dim rng As Range
Dim rng2 As Range
Dim rngTemp As Range
Dim arrCellGroups() As String
Dim l As String
Dim i As Integer
Dim j As Integer

With Worksheets("Sheet1").AutoFilter.Range
' On Error Resume Next
' Set rng2 = Worksheets("Sheet1").AutoFi lter.Range.Offset(1, 0).Resize(.Rows.Count - 1, 1) _
' .SpecialCells(xlCellTypeVisible)

Set rng2 = Worksheets("Sheet1").AutoFilter.Range.SpecialCells(xlCellTypeVisible)

arrCellGroups = Split(rng2.Address, ",")

' The rng2.address only can hold 256 strings. this is my problem

For i = 0 To UBound(arrCellGroups)

Set rngTemp = Range(arrCellGroups(i))

If rngTemp.Rows.Count >= 1 Then
For j = 1 To rngTemp.Rows.Count

' In this, I would write some code to access internet and return the data to
' my worksheet. The following line just an example"

rngTemp.Cells(j, 3) = "test"

Next j

End If
Next i


On Error GoTo 0
End With