searching for a better way to hide rows
 

I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report displays the
columns in a different order.
For some of the reports, I need to hide records that match certain criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust to the
different column orders?
Thanks,

--
tj

searching for a better way to hide rows
 

To the columns have headers that identify the correct column to filter on?

then you could get the column location by finding this column header - then
apply an advanced filter.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report displays
the
columns in a different order.
For some of the reports, I need to hide records that match certain
criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust to the
different column orders?
Thanks,

--
tj

searching for a better way to hide rows
 

Yes, there are Column Headers. I'm about to look through google, but does
anyone have examples of programmatic use of the Aadvanced Filter?
--
tj


"Tom Ogilvy" wrote:

To the columns have headers that identify the correct column to filter on?

then you could get the column location by finding this column header - then
apply an advanced filter.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report displays
the
columns in a different order.
For some of the reports, I need to hide records that match certain
criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust to the
different column orders?
Thanks,

--
tj

searching for a better way to hide rows
 

It is a single line of code.

Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria")

(See VBA help)

the trick is to set up the criteria range with the correct criteria.
(advanced filter requires a physical range that has headers and the filter
value. Identical header on the same row criteria represents and AND
condition. On multiple rows under a single header representes OR
conditions.

I only suggest an Advanced filter because you appear to require more than
two criteria.

--
Regards,
Tom Ogilvy


"tjtjjtjt" wrote in message
...
Yes, there are Column Headers. I'm about to look through google, but does
anyone have examples of programmatic use of the Aadvanced Filter?
--
tj


"Tom Ogilvy" wrote:

To the columns have headers that identify the correct column to filter
on?

then you could get the column location by finding this column header -
then
apply an advanced filter.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report
displays
the
columns in a different order.
For some of the reports, I need to hide records that match certain
criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust to
the
different column orders?
Thanks,

--
tj

searching for a better way to hide rows
 

Thanks, again.
In some cases I do need more than two criteria. I didn't realize I had to
use a range until I read some old posts. I should've looked in the help
first--at work, my computer doesn't have office fully installed. One of the
casualties is the VB Help. Still waiting to get it fully installed. I'm home
today, but I'm out of the habit of looking.

--
tj


"Tom Ogilvy" wrote:

It is a single line of code.

Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria")

(See VBA help)

the trick is to set up the criteria range with the correct criteria.
(advanced filter requires a physical range that has headers and the filter
value. Identical header on the same row criteria represents and AND
condition. On multiple rows under a single header representes OR
conditions.

I only suggest an Advanced filter because you appear to require more than
two criteria.

--
Regards,
Tom Ogilvy


"tjtjjtjt" wrote in message
...
Yes, there are Column Headers. I'm about to look through google, but does
anyone have examples of programmatic use of the Aadvanced Filter?
--
tj


"Tom Ogilvy" wrote:

To the columns have headers that identify the correct column to filter
on?

then you could get the column location by finding this column header -
then
apply an advanced filter.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report
displays
the
columns in a different order.
For some of the reports, I need to hide records that match certain
criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust to
the
different column orders?
Thanks,

--
tj

searching for a better way to hide rows
 

Hi. Just a general discussion. Your macro scans cells left to right, then
top to bottom. If you have 100 columns in your UsedRange, and your macro
hid a row based on column 2, your macro will continue to look at the
remaining 98 columns before moving on to the next row.
As a general idea, perhaps if your macro hides a Row, then skip on to the
next row.

Sub ScheduleHideRows()
Dim rngRows As Range
Dim Cell As Range

Application.ScreenUpdating = False
ActiveSheet.UsedRange 'Reset

For Each rngRows In ActiveSheet.UsedRange.Rows
For Each Cell In rngRows.Cells
Select Case Cell.Value
Case JP1, JP2, CDay, RR
rngRows.EntireRow.Hidden = True
Exit For
End Select
Next Cell
Next rngRows
End Sub

HTH :)
--
Dana DeLouis
Win XP & Office 2003


"tjtjjtjt" wrote in message
...
I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report displays
the
columns in a different order.
For some of the reports, I need to hide records that match certain
criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust to the
different column orders?
Thanks,

--
tj

searching for a better way to hide rows
 

Advanced filter is Excel worksheet functionality - so the help is in Excel
itself - not VBA.

Hopefully they have that help installed. that said, it can be
implemented/controlled via VBA. It is usually best to utilize Excel builtin
functionality in VBA when it makes sense (and it usually does).

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
It is a single line of code.

Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=Range("Criteria")

(See VBA help)

the trick is to set up the criteria range with the correct criteria.
(advanced filter requires a physical range that has headers and the filter
value. Identical header on the same row criteria represents and AND
condition. On multiple rows under a single header representes OR
conditions.

I only suggest an Advanced filter because you appear to require more than
two criteria.

--
Regards,
Tom Ogilvy


"tjtjjtjt" wrote in message
...
Yes, there are Column Headers. I'm about to look through google, but
does
anyone have examples of programmatic use of the Aadvanced Filter?
--
tj


"Tom Ogilvy" wrote:

To the columns have headers that identify the correct column to filter
on?

then you could get the column location by finding this column header -
then
apply an advanced filter.

--
Regards,
Tom Ogilvy

"tjtjjtjt" wrote in message
...
I have the following macro, which is doing what I want it to do:
Sub ScheduleHideRows()
Application.ScreenUpdating = False
Dim rws As Range
For Each rws In ActiveSheet.UsedRange
If rws.Value = JP1 Then rws.EntireRow.Hidden = True
If rws.Value = JP2 Then rws.EntireRow.Hidden = True
If rws.Value = CDay Then rws.EntireRow.Hidden = True
If rws.Value = RR Then rws.EntireRow.Hidden = True
Next rws
End Sub

Is there a better way?
Here is the scenario:
I run several different reports from the same database.
Each report contains many of the same columns, but each report
displays
the
columns in a different order.
For some of the reports, I need to hide records that match certain
criteria.
The criteria have been defined with public constants.
Is it possible to make an intelligent filter macro that will adjust
to
the
different column orders?
Thanks,

--
tj