Excel Autofilter Visible Range

I am applying autofilters to two different tables in different spreadsheets via macro.

Later for the VISIBLE Range after autofilter, I need to perform some calculations between two tables.

How can I set the Range of visible records ?
for example to get first and last row of visible Records ? and to perform calculations only on visible rows !
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Assuming its A Column then you can simply do this..

set rng=range("A:A").SpecialCells(xlCellTypeVisible)

Open in new window

Patrick MatthewsCommented:
Can you post a (sanitized) sample file indicating what you are trying to do?
cynxAuthor Commented:
@matthewspatrick: What I am trying to do is this: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25989625.html

However, In my main code, There is more data in both tables (which is processed) and above action is not required on them.

Hence I am filtering data in both my tables based on certain flags and then Plan to perform above(Link) action to visible data (not processed data).

Since, if first time the attached(link) action is performed on the two tables, after that I am inserting more data to the first table from other macro/source, hence I need to perform above action again only for new data and so on...

@saurabh726: I have read on forums that specialcells method crashes for large data, in my case there would be close to 60k records ! if that is not true could you please explain how can i get my first and last row from above set range ?

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Here is an example... if any rows or columns are hidden they will not be included in the areas
selected by the SpecialCells method. This example multiplies each cell by 2 for each time the
macro is run:
    Dim a As Range
    Dim r As Range
    Set r = Range("C2:E8").SpecialCells(xlCellTypeVisible)
    For Each a In r.Areas
        For i = 1 To a.Cells.Count
           a.Cells(i).Value = a.Cells(i).Value * 2

Open in new window

Saurabh Singh TeotiaCommented:
Yes that won't work for the large data set, the only way i can think that is to do a loop statement like this..

Dim rng As Range, cell As Range, r As Range

    Set rng = Range("a2:a" & Cells(Cells.Rows.Count, "a").End(xlUp).Row)

    For Each cell In rng
        If cell.EntireRow.Hidden = False Then
            If r Is Nothing Then
                Set r = cell
                Set r = Union(r, cell)
            End If
        End If
    Next cell

    MsgBox r.Address

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
This gives you an idea for how to retrieve the first and last row of a multiple range object:

    Dim a As Range
    Dim r As Range
    Set r = Range("C2:D8").SpecialCells(xlCellTypeVisible)
    firstRowRange = r.Areas(1).Rows(1).Address
    firstRow = Range(firstRowRange).EntireRow.Address
    lastRowRange = r.Areas(r.Areas.Count).Rows(r.Areas(r.Areas.Count).Rows.Count).Address
    lastRow = Range(lastRowRange).EntireRow.Address
    Debug.Print firstRowRange
    Debug.Print firstRow
    Debug.Print lastRowRange
    Debug.Print lastRow

Open in new window

cynxAuthor Commented:
saurabh726's method was what fits into my requirements. However I would also give somepoints to yogi4life, since that would be an ideal solution for less volume of data.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.