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 !
LVL 1
cynxAsked:
Who is Participating?
 
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..
Saurabh...

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
            Else
                Set r = Union(r, cell)
            End If
        End If
    Next cell

    MsgBox r.Address

Open in new window

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

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

Open in new window

0
 
Patrick MatthewsCommented:
Can you post a (sanitized) sample file indicating what you are trying to do?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 ?




0
 
yogi4lifeCommented:
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
           Next
    Next

Open in new window

0
 
yogi4lifeCommented:
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

0
 
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.