Alternative for .SpecialCells(xlCellTypeVisible)

[bluenceo]

Hi, I need some help with the below - I have a spreadsheet which contains a filter and "rng=.SpecialCells(xlCellTypeVisible)". For example, it will always copy from range A17 to G25". I wish for it to start from B17 and end at the last G cell of the filter. Can anyone enlighten me on how to achieve this? Thanks.

Set Cws = Worksheets.Add
    FilterRange.Columns(FieldNum).AdvancedFilter _
            Action:=xlFilterCopy, _
            CopyToRange:=Cws.Range("A1"), _
            CriteriaRange:="", Unique:=True


    'Count of the unique values + the header cell
    Rcount = Application.WorksheetFunction.CountA(Cws.Columns(1))


    'If there are unique values start the loop
    If Rcount >= 2 Then
        For Rnum = 2 To Rcount


            'Filter the FilterRange on the FieldNum column
            FilterRange.AutoFilter Field:=FieldNum, _
                                   Criteria1:=Cws.Cells(Rnum, 1).Value


            'Look for the mail address in the MailInfo worksheet
            mailAddress = ""
                      
            On Error Resume Next
            mailAddress = Application.WorksheetFunction. _
                          VLookup(Cws.Cells(Rnum, 1).Value, _
                                Worksheets("Mailinfo").Range("A1:C" & _
                                Worksheets("Mailinfo").Rows.Count), 3, False)
            On Error GoTo 0
            
            clientAddress = Application.WorksheetFunction. _
                          VLookup(Cws.Cells(Rnum, 1).Value, _
                                Worksheets("Mailinfo").Range("A1:B" & _
                                Worksheets("Mailinfo").Rows.Count), 1, False)




            If mailAddress <> "" Then
                With Ash.AutoFilter.Range
                    On Error Resume Next
[COLOR=#ff0000][FONT=arial black]                    Set rng = .SpecialCells(xlCellTypeVisible)[/FONT][/COLOR]
                    On Error GoTo 0
                End With


                Set OutMail = OutApp.CreateItem(0)

 

[Norie]

What exactly are you looking for an 'alternative' to?

 

[bluenceo]

For example, to change it into ".Range("B17", .Range("GXX").End(xlUp))". However, G cell is dynamic and will change accordingly to the filter within the macro.

 

[Norie]

What do you want to change to ".Range("B17", .Range("GXX").End(xlUp))"?

The range being filtered?

 

[bluenceo]

What do you want to change to ".Range("B17", .Range("GXX").End(xlUp))"?

The range being filtered?

Hi Norie,

Yes, I will like it to capture the range being filterd - from B17 to the end cell in G column.

 

[Norie]

The last visible row in column G might change because of the filter but the last row of data in column G won't.