Filter / Range question - Xtreme Visual Basic Talk

07-22-2010, 02:15 AM

valo

Newcomer

Join Date: Jul 2008

Posts: 22

Filter / Range question

I have a table with few columns and lots of rows, which I filter by one field. This field can contain up to three kinds of values, say A, B and C, and after each filtering I need to copy the filtered data into another sheet and work on it there.

The problem comes when when there's no data corresponding to C value (there's no C value in the table). So the filtering process returns only the table header, which is fine, but I have no ideea of how to tell the compiler that there's no more data to select. The copy method selects all the cells in the sheet (the table header and ALL the blank cells) and then I paste them in the new sheet. When I do that, the scroll bar shrinks to its limits and the Ctrl+End shortcut, which initially was placing the cursor on the last cell of the table, now places it on the 'Cells(1048576, X)', and unfortunately this is not acceptable.

The question is how to avoid this situation? How can I figure out if the filtering returned rows or not? The Range("A1").End(xlDown).Row and Offset(x,y) commands are useless, since I navigate through filtered (basically hidden) cells.

07-22-2010, 03:08 AM

Colin Legg

Out Of Office

Retired Moderator
* Expert *

Join Date: Mar 2005

Location: London, UK

Posts: 3,398

Hi,

One way is to use Range.SpecialCells(xlCellTypeVisible).

Here's an example with a little defensive coding:

Code:

Sub Test()

    Dim rngFilter As Range
    Dim rngVisible As Range
    
    'is there an autofilter on sheet1?
    If Sheet1.AutoFilterMode Then
    
        'are any filters being used?
        If Sheet1.FilterMode Then
        
            'get a reference to the autofilter range
            'excluding the header row
            With Sheet1.AutoFilter.Range
                Set rngFilter = .Offset(1).Resize(.Rows.Count - 1, .Columns.Count)
            End With
        End If
    End If
    
    
    If rngFilter Is Nothing Then
        MsgBox "no filtering is being applied!"
        
    Else
        
        'find the visible cells, if there are none then an error will be raised
        On Error Resume Next
        Set rngVisible = rngFilter.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0

        If rngVisible Is Nothing Then
            MsgBox "there are no visible cells!"
        Else
            MsgBox rngVisible.Address
        End If
        
    End If
    
End Sub

This was written for XL 2003. The object model changed slightly in XL 2007, so if you are using XL 2007 or later then you may have to tweak the code a little.

__________________
RAD Excel Blog

07-22-2010, 04:36 AM

valo

Newcomer

Join Date: Jul 2008

Posts: 22

That worked just fine, Colin, thank you!

Quote:

Originally Posted by Colin Legg

One way is to use Range.SpecialCells(xlCellTypeVisible).

What other option would I have?