Hi Excel Forum,

Is there a workaround or solution?

I would like to display/ view Criteria based Filtered Visible (Cells
Rows together with specific Non-Filtered (Cells) Rows. Un-Filtere
Data starts in Row 11.

Example:

Un-Filtered Row Data:
Row Grid Ref: 1 2 3 4 5 - 1000 sequentially


Filtered Visible Row Data:
(Based on Advanced Filter Criteria & Filter In-place):
Row Grid Ref: 2 3 6 30 35 50 68 79 85 90 100

Scenario:
I would like to display the Filtered Visible Rows with each Row tha
is sequentially Before (above) it and sequentially After (below) it.
So ultimately, from the sample data above, the Rows displayed would b
Filtered Visible Rows + Rows (sequentially)Before + Rows (sequentially
After the actual Filtered Visible Rows:

Row Grid Ref: 1 2 3 4 5 6 7 29 30 31 34 35 36 49 50 51 67 68 69 78 7
80 84 85 86 89 90 91 99 100 101

Is it possible using VBA to combine the Filtered Visible Rows an
Un-Filtered Rows as mentioned above?

Please advise, if possible assist with a working example.

Kind regards,
QT

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

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 it
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 want 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/

I think that this does what you want:

Option Explicit
Sub testme()

Dim rngF As Range
Dim myCell As Range

With ActiveSheet
With .AutoFilter.Range
Set rngF = Nothing
On Error Resume Next
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
'nothing showing!
Exit Sub
End If
For Each myCell In rngF.Cells
With myCell
.Offset(-1, 0).EntireRow.Hidden = False
.Offset(1, 0).EntireRow.Hidden = False
End With
Next myCell
End With
End With

End Sub

"QTE <" wrote:

Hi Excel Forum,

Is there a workaround or solution?

I would like to display/ view Criteria based Filtered Visible (Cells)
Rows together with specific Non-Filtered (Cells) Rows. Un-Filtered
Data starts in Row 11.

Example:

Un-Filtered Row Data:
Row Grid Ref: 1 2 3 4 5 - 1000 sequentially

Filtered Visible Row Data:
(Based on Advanced Filter Criteria & Filter In-place):
Row Grid Ref: 2 3 6 30 35 50 68 79 85 90 100

Scenario:
I would like to display the Filtered Visible Rows with each Row that
is sequentially Before (above) it and sequentially After (below) it.
So ultimately, from the sample data above, the Rows displayed would be
Filtered Visible Rows + Rows (sequentially)Before + Rows (sequentially)
After the actual Filtered Visible Rows:

Row Grid Ref: 1 2 3 4 5 6 7 29 30 31 34 35 36 49 50 51 67 68 69 78 79
80 84 85 86 89 90 91 99 100 101

Is it possible using VBA to combine the Filtered Visible Rows and
Un-Filtered Rows as mentioned above?

Please advise, if possible assist with a working example.

Kind regards,
QTE

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

--

Dave Peterson

Hi Dave,

Thank you for assistance. Unfortunately, when I run the procedure i
displays ONLY the AutoFiltered Visible Cells / Rows and NOT th
required display of Filtered Visible Rows, together with the Row
Before and After the Visible Rows.

I am using an ancient version of Excel: ver 7. I made a couple o
changes to the procedure as I couldn't get it to run as it stood usin
the "With" statements with AutoFilter; and I changed th
SpecialCells(xlCellTypeVisible) to (xlVisible).

I've tried to figure out what might be causing the problem with "n
success". Would appreciate further assistance.

Sub testme()
Set MyObject = Sheets("test")
Dim rngF As Range
Dim myCell As Range
With ActiveSheet

'With .AutoFilter.Range

MyObject.Range("A9").AutoFilter _
Field:=16, _
Criteria1:="103/5"

Set rngF = Nothing
On Error Resume Next
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
'.Cells.SpecialCells(xlCellTypeVisible)
.Cells.SpecialCells(xlVisible)
On Error GoTo 0
If rngF Is Nothing Then
'nothing showing!
Exit Sub
End If
For Each myCell In rngF.Cells
With myCell
.Offset(-1, 0).EntireRow.Hidden = False
.Offset(1, 0).EntireRow.Hidden = False
End With
Next myCell
'End With
End With
End Sub
'----------------------------------------------

Can you think of anything that might be stopping the display of th
Rows Before and After the Filtered Visible Cells.

Hope you can spare the time.
Kind regards,
QTE

Dave Peterson wrote:
*I think that this does what you want:

Option Explicit
Sub testme()

Dim rngF As Range
Dim myCell As Range

With ActiveSheet
With .AutoFilter.Range
Set rngF = Nothing
On Error Resume Next
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
'nothing showing!
Exit Sub
End If
For Each myCell In rngF.Cells
With myCell
.Offset(-1, 0).EntireRow.Hidden = False
.Offset(1, 0).EntireRow.Hidden = False
End With
Next myCell
End With
End With

End Sub

"QTE <" wrote:

Hi Excel Forum,

Is there a workaround or solution?

I would like to display/ view Criteria based Filtered Visibl
(Cells)
Rows together with specific Non-Filtered (Cells) Rows.
Un-Filtered
Data starts in Row 11.

Example:

Un-Filtered Row Data:
Row Grid Ref: 1 2 3 4 5 - 1000 sequentially

Filtered Visible Row Data:
(Based on Advanced Filter Criteria & Filter In-place):
Row Grid Ref: 2 3 6 30 35 50 68 79 85 90 100

Scenario:
I would like to display the Filtered Visible Rows with each Ro
that
is sequentially Before (above) it and sequentially After (below
it.
So ultimately, from the sample data above, the Rows displayed woul
be
Filtered Visible Rows + Rows (sequentially)Before + Row
(sequentially)
After the actual Filtered Visible Rows:

Row Grid Ref: 1 2 3 4 5 6 7 29 30 31 34 35 36 49 50 51 67 68 69 7
79
80 84 85 86 89 90 91 99 100 101

Is it possible using VBA to combine the Filtered Visible Rows and
Un-Filtered Rows as mentioned above?

Please advise, if possible assist with a working example.

Kind regards,
QTE

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

--

Dave Peterson


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

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

I'm not sure when .autofilter.range came into existence either.

Here's a different version that _may_ work (I don't have xl95 available anymore
to test).

Option Explicit
Sub testme2()

Dim Wks As Worksheet
Dim rngF As Range
Dim myCell As Range

Set Wks = Sheets("test")

With Wks
With .Range("_FilterDatabase")
Set rngF = Nothing
On Error Resume Next
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlVisible)
On Error GoTo 0
If rngF Is Nothing Then
'nothing showing!
Exit Sub
End If
For Each myCell In rngF.Cells
With myCell
.Offset(-1, 0).EntireRow.Hidden = False
.Offset(1, 0).EntireRow.Hidden = False
End With
Next myCell
End With
End With
End Sub

I don't recall anything else that needs changing (you got the xlVisible and this
used "_filterdatabase" instead of .autofilter.range.

ps. This assumes that the worksheet has had data|filter already applied). If
you're going to add the filter to the routine, you may want to do that after you
have this portion working correctly. (Too much stuff means that lots can go
wrong!)




"QTE <" wrote:

Hi Dave,

Thank you for assistance. Unfortunately, when I run the procedure it
displays ONLY the AutoFiltered Visible Cells / Rows and NOT the
required display of Filtered Visible Rows, together with the Rows
Before and After the Visible Rows.

I am using an ancient version of Excel: ver 7. I made a couple of
changes to the procedure as I couldn't get it to run as it stood using
the "With" statements with AutoFilter; and I changed the
SpecialCells(xlCellTypeVisible) to (xlVisible).

I've tried to figure out what might be causing the problem with "no
success". Would appreciate further assistance.

Sub testme()
Set MyObject = Sheets("test")
Dim rngF As Range
Dim myCell As Range
With ActiveSheet

'With .AutoFilter.Range

MyObject.Range("A9").AutoFilter _
Field:=16, _
Criteria1:="103/5"

Set rngF = Nothing
On Error Resume Next
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
'.Cells.SpecialCells(xlCellTypeVisible)
Cells.SpecialCells(xlVisible)
On Error GoTo 0
If rngF Is Nothing Then
'nothing showing!
Exit Sub
End If
For Each myCell In rngF.Cells
With myCell
Offset(-1, 0).EntireRow.Hidden = False
Offset(1, 0).EntireRow.Hidden = False
End With
Next myCell
'End With
End With
End Sub
'----------------------------------------------

Can you think of anything that might be stopping the display of the
Rows Before and After the Filtered Visible Cells.

Hope you can spare the time.
Kind regards,
QTE

Dave Peterson wrote:
*I think that this does what you want:

Option Explicit
Sub testme()

Dim rngF As Range
Dim myCell As Range

With ActiveSheet
With .AutoFilter.Range
Set rngF = Nothing
On Error Resume Next
Set rngF = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If rngF Is Nothing Then
'nothing showing!
Exit Sub
End If
For Each myCell In rngF.Cells
With myCell
.Offset(-1, 0).EntireRow.Hidden = False
.Offset(1, 0).EntireRow.Hidden = False
End With
Next myCell
End With
End With

End Sub

"QTE <" wrote:

Hi Excel Forum,

Is there a workaround or solution?

I would like to display/ view Criteria based Filtered Visible
(Cells)
Rows together with specific Non-Filtered (Cells) Rows.
Un-Filtered
Data starts in Row 11.

Example:

Un-Filtered Row Data:
Row Grid Ref: 1 2 3 4 5 - 1000 sequentially

Filtered Visible Row Data:
(Based on Advanced Filter Criteria & Filter In-place):
Row Grid Ref: 2 3 6 30 35 50 68 79 85 90 100

Scenario:
I would like to display the Filtered Visible Rows with each Row
that
is sequentially Before (above) it and sequentially After (below)
it.
So ultimately, from the sample data above, the Rows displayed would
be
Filtered Visible Rows + Rows (sequentially)Before + Rows
(sequentially)
After the actual Filtered Visible Rows:

Row Grid Ref: 1 2 3 4 5 6 7 29 30 31 34 35 36 49 50 51 67 68 69 78
79
80 84 85 86 89 90 91 99 100 101

Is it possible using VBA to combine the Filtered Visible Rows and
Un-Filtered Rows as mentioned above?

Please advise, if possible assist with a working example.

Kind regards,
QTE

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

--

Dave Peterson
*

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

--

Dave Peterson

First, I'd try that other version first. This one does too much work. But I
did add some general comments. (But not enough to make it work.)

"QTE <" wrote:

Hi MSP77079,

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

The procedure stops at the line below:

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

This was in the middle of this loop:

For i = FirstRow To LastRow
If Cells(i, "16").EntireRow.Hidden = False Then

But FirstRow was never initialized. And since it's not inititalized (and
declared as integer???), it starts at 0.

So you have
If cells(0, 16).entirerow.hidden = false then
or
If Cells(0, "p").EntireRow.Hidden = False Then

(and drop the double quotes. You can either use the column number (16) or the
column letter ("P"), but you don't want to do "16")



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

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

Should the variables be declared As:

I would declare all of the whole numbers as Longs.

Dim LastRow As Long
Dim FirstRow As Long
Dim n As Long
Dim i As Long
Dim VizRows() As Long

Since you're still using xl95, Integers would be ok, but it turns out Longs make
the computer work less and are faster. And when you upgrade to xl97 <bg,
you'll be happy.

Integers can go up to 32767 (which is bigger than the number of rows you have
per worksheet 16384).

But in xl97+, the number of rows can grow to 65536. If you use longs now, you
won't have to fix things tomorrow.

And be careful:

dim lastrow, firstrow as long
declares the firstrow as long, but lastrow is declared as a variant.

I like this style:
dim LastRow as long
dim FirstRow as long
(easier to copy/paste/comment out)

but you could do this:

dim Lastrow as long, firstrow as long




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

MyObject should be declared as a worksheet:
dim myobject as worksheet
(I'd use a more meaningfull name, too.)
dim myWks as worksheet

For the most part, you don't have to select anything or activate anything. You
can work directly against the object (worksheet/range/workbook/shape/etc) you
want.




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

Set myRange = MyObject.Range("Database")

How did you know that "Database" even existed? (See previous post for
..autofilter.range and it's earlier incarnation ("_FilterDatabase"). I think
DataBase is used with Data|Form (maybe it was a coincidence that you used this
on the range you filtered???)




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

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

and determine the firstrow of the range

with myrng
lastrow = .cells(.cells.count).row
firstrow = .row
'or
'firstrow = .row + 1 'to avoid headers.
end with



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

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

'Next, find out which rows are visible.


Since you're using vizrows as an array, you have to treat it better.

Did you notice this at the top?

Dim VizRows() As Long

Those ()'s mean that I'm gonna use it to hold more than one thing.

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

The preserve means that I don't want to lose the previous plopped in values.

Later on when I want to go through those values, I can use:

dim iCtr as long
if n 0 then 'check to see first.
for ictr = lbound(vizrows) to ubound(vizrows)
'do something
next ictr
else
msgbox "no visible rows"
end if



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 it
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 want
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/

--

Dave Peterson

Hi Dave,

A huge thank you: all your time and effort greatly appreciated.
Thank you for correcting my improper usage of Object when declaring th
Worksheet variable: it was part of the problem.

The testme2 procedure works brilliantly. As suggested, I first trie
it without the autofilter routine: no problems. I then added th
autofilter routine and is working fine.

Thank you for explaining posting from MSP77079. Very helpful comments
I learnt a lot.

All is well..... for now!

My reference to "Database" was made on the understanding that Exce
automatically creates a hidden named range called _FilterDatabase whe
AutoFilter is on. So, I thought I'd play with it, to see what it ca
do. Sometimes I come unstuck. A Little knowledge can be a dangerou
thing but then its good to try things out in a controlled, tes
environment.

My information pertaining to "Database" came from a Microsoft Knowlegd
Base Article on Autofiltering
http://msdn.microsoft.com/library/en...TAutofilter.as


As a separate issue: I wanted to see what the effect would be whe
using "Database" as a workaround to prevent Empty Advanced Filte
Criteria Range or List Range Box by Naming and Defining my List Rang
as "Database".

Kind regards
QT

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

Glad it worked out.

"QTE <" wrote:

Hi Dave,

A huge thank you: all your time and effort greatly appreciated.
Thank you for correcting my improper usage of Object when declaring the
Worksheet variable: it was part of the problem.

The testme2 procedure works brilliantly. As suggested, I first tried
it without the autofilter routine: no problems. I then added the
autofilter routine and is working fine.

Thank you for explaining posting from MSP77079. Very helpful comments.
I learnt a lot.

All is well..... for now!

My reference to "Database" was made on the understanding that Excel
automatically creates a hidden named range called _FilterDatabase when
AutoFilter is on. So, I thought I'd play with it, to see what it can
do. Sometimes I come unstuck. A Little knowledge can be a dangerous
thing but then its good to try things out in a controlled, test
environment.

My information pertaining to "Database" came from a Microsoft Knowlegde
Base Article on Autofiltering:
http://msdn.microsoft.com/library/en...Autofilter.asp

As a separate issue: I wanted to see what the effect would be when
using "Database" as a workaround to prevent Empty Advanced Filter
Criteria Range or List Range Box by Naming and Defining my List Range
as "Database".

Kind regards
QTE

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

--

Dave Peterson