Filter / Range question

07-22-2010, 02:15 AM
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.

Colin Legg
07-22-2010, 03:08 AM

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

Here's an example with a little defensive coding:

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!"


'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!"
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.

07-22-2010, 04:36 AM
That worked just fine, Colin, thank you!

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

What other option would I have?

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum