[SOLVED] Excel VBA advanced filter

Excel VBA advanced filter - uppercase

I'm using the following code to do first an advanced filter. Then I need to change the filtered range (except headers) to uppercase avoiding looping as it would slow things too much. But there is something wrong in the last line, and I get a #VALUE! error in all filtered cells. does anybody know what is not working here?

Sub upper()
    Dim rgdata As Range
    Dim rgcriteria As Range
    
    
    'filter
    Set rgdata = Worksheets("sheet1").Range("A16").CurrentRegion
    Set rgcriteria = Worksheets("sheet1").Range("A12").CurrentRegion
    rgdata.AdvancedFilter xlFilterInPlace, rgcriteria  
    
    
    'upper
    Dim rngup As Range
  Set rngup =  worksheets("sheet1").Range("A16").CurrentRegion.SpecialCells(xlCellTypeVisible).offset(1,0)
    
    rngup = evaluate("index(upper(" & rngup.address & "),)")

End Sub

Solution

Offset(1,0) should be used before SpecialCells
Filtered range may be a non-continuous range, loop through each areas in it.

    'upper
    Dim rngup As Range
    With .Range("A16").CurrentRegion
        Set rngup = .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
    End With
    Dim c As Range
    If Not rngup Is Nothing Then
        For Each c In rngup.Areas
            c.Value = Evaluate("upper(" & c.Address & ")")
        Next
    End If