I am writing a VBA script in Excel for processing a visilbe row after a autofilter. I made use of your sample of the creating smart tags for autofilter results sample in MSDN.
However, I find that the sample cannot work properly when there is a lot of distinct group of cell. The rng2.address cannot hold the whole string greater than 256.
If I am right, 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, ",")
MsgBox rng2.Address
MsgBox UBound(arrCellGroups)
For i = 0 To UBound(arrCellGroups)
' MsgBox UBound(arrCellGroups)
Set rngTemp = Range(arrCellGroups(i))
' MsgBox rngTemp.Rows.Count
If rngTemp.Rows.Count >= 1 Then
For j = 1 To rngTemp.Rows.Count
rngTemp.Cells(j, 3) = "test"
Next j
End If
Next i
On Error GoTo 0
End With
Bookmarks