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