Hi MSP77079,

Thank you for assistance. I've been a while getting back to yo
because I've been trying to figure out an error message which pops u
when I run the procedu haven't sussed it!

The procedure stops at the line below:

If cells(i, 16).entirerow.hidden = false then

Error Message = Run-Time Error "1004" Cells method of Application clas
failed.

I've changed your procedure slightly. I've not used the Current Regio
method of finding the entire Range of the Data Table as I've got empt
columns in my Data Range /Table: I've used a Named Range("Database").

Should the variables be declared As:

Dim myRange As Range
Dim LastRow, FirstRow As Integer? / Variant?
Dim n, i AS Integer
Dim VizRows As Integer

Please advise.

This is Revised Version:

Sub Example()
Dim myRange
Dim LastRow, FirstRow
Dim n, i
Dim VizRows

Set MyObject = Sheets("example")

MyObject.Activate

'First, know the range you are working with. Best way to know what i
is ... _
select one cell then find the entire data table by:

Set myRange = MyObject.Range("Database")

'Second, determine the number of rows in that range:

LastRow = myRange.Cells(myRange.Cells.Count).Row

'Third, apply your filter.
myRange.Select
'Selection.AutoFilter Field:=??, Criteria1:="????"
'where ?? is the number of the column and ???? is the filter you wan
to apply

Selection.AutoFilter Field:=16, Criteria1:="103/5"

'Next, find out which rows are visible.

n = 0
For i = FirstRow To LastRow
If Cells(i, "16").EntireRow.Hidden = False Then
n = n + 1
VizRows(n) = i
End If
Next i

'Now, turn off the filter:

If ActiveSheet.FilterMode = True Then ActiveSheet.AutoFilterMode
False

'Now, hide all rows.

myRange.EntireRow.Hidden = True

'Now, unhide all rows that you want to unhide:

For i = 1 To n
Cells(VizRows(n) - 1, 1).EntireRow.Hidden = False
Cells(VizRows(n), 1).EntireRow.Hidden = False
Cells(VizRows(n) + 1, 1).EntireRow.Hidden = False
Next i
End Sub

Would appreciate further assistance.

Kind regards,
QTE

MSP77079 wrote:
*Sure, it can be done. But I can't tell you how to do it ELEGANTLY.

First, know the range you are working with. Best way to know what i
is ... select one cell then find the entire data table by:

Set myRange = Range(one cell in data table).CurrentRegion

Second, determine the number of rows in that range:
LastRow = myRange.Cells(myRange.Cells.Count).row

Third, apply your filter.

myRange.Select
Selection.AutoFilter Field:=??, Criteria1:="????"
where ?? is the number of the column and ???? is the filter you wan
to apply

Next, find out which rows are visible.
n = 0
For i = FirstRow to LastRow
If cells(i, ??).entirerow.hidden = false then
n = n + 1
vizRows(n) = i
End if
Next i

Now, turn off the filter:
If ActiveSheet.FilterMode = True Then ActiveSheet.AutoFilterMode
False

Now, hide all rows.
myRange.entirerow.hidden = True

Now, unhide all rows that you want to unhide:
For i = 1 to n
cells(vizRows(n)-1, 1).entirerow.hidden = false
cells(vizRows(n), 1).entirerow.hidden = false
cells(vizRows(n)+1, 1).entirerow.hidden = false
next i

--
Message posted from http://www.ExcelForum.com