Setting an Autofiltered Table as a Range using VBA

[greenwell]

Good evening,

I have written a report writing program within excel using vba and I have a problem when I try to assign my filtered data to a range.

All my data lives in a table called T_RESIDENTS which I set in the code as:

Set tblresidents = .Sheets("Residents").ListObjects("T_RESIDENTS")

When I come to pull through this data I use:

Set filterresults = tblresidents.Autofilter.Range.SpecialCells(xlCellTypeVisible)

This range (filterresults) however contains no data! if there is no autofilter on the table the whole body of the table is pulled through but when any sort of filtering is put on the table, unless only the first row is available where is will pull through row 1, no other rows are pulled! Its so frustrating and I can copy and paste the range using a variation of the code (with . copy at the end) then pasting on another sheet but this isn't what I want.


I want to be able to set my filterresults range all the visible data from y table once a filter has been applied. I have the whole code but I'm unsure where to post it, hopefully this will be enough info!


Hoping someone more experienced than me can help.
I'm using excel 2013 on windows 10 if that information is needed.

many thanks,

Andy

<strike></strike>

 

[MickG]

This worked for Me!!!!

Dim tblresidents As Range, filterresults As Range
Set tblresidents = Sheets("Residents").ListObjects("T_RESIDENTS").DataBodyRange
    MsgBox tblresidents.Address
Set filterresults = tblresidents.SpecialCells(xlCellTypeVisible)
    MsgBox filterresults.Address

 

[greenwell]

MickG thank you for your reply, I must have overcomplicated it as when I used your suggestion it works fine!! Thank you and I apologise for the late reply to you. Next question how do I mark you as having saved me and close this thread....
thanks again,

Andy