Selecting two ranges of unadjacent cells

C

CTInt04

Problem:
I have a list of all the dates in the year. I want to hide all of the dates that are not May. I want to hide all the dates before and after May. How do I hide all the dates except May using a macro in VB.

Note: Specific cells cant be hidden, because the position of the dates in May might change. I can make a For...Next loop that does this, but it hides each row one by one, and takes tooooooo long.
 
T

Tom Ogilvy

Turn on the macro recorder and apply an autofilter to your data
(data=>Filter=>Autofilter). In the column with the dates select custom and
choose greater than May 30, 2004 OR earlier than May 1, 2004. If you want
May over multiple years, then use a helper column and put a formula in that
returns the number of the month, then filter on that.

Turn off the macro recorder and generalize the code if you wish/need to.

--
Regards,
Tom Ogilvy

CTInt04 said:
Problem:
I have a list of all the dates in the year. I want to hide all of the
Click to expand...
dates that are not May. I want to hide all the dates before and after May.
How do I hide all the dates except May using a macro in VB.
Note: Specific cells cant be hidden, because the position of the dates in
Click to expand...
May might change. I can make a For...Next loop that does this, but it hides
each row one by one, and takes tooooooo long.
 
B

Bob Phillips

Show us the code that takes to long.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

CTInt04 said:
Problem:
I have a list of all the dates in the year. I want to hide all of the
Click to expand...
dates that are not May. I want to hide all the dates before and after May.
How do I hide all the dates except May using a macro in VB.
Note: Specific cells cant be hidden, because the position of the dates in
Click to expand...
May might change. I can make a For...Next loop that does this, but it hides
each row one by one, and takes tooooooo long.
 
J

Jon Peltier

Do you want to hide all rows with any month besides May in a given
column? select the range, and select Autofilter on the Data menu. Click
on the dropdown arrow atop the Month column, select May, and all other
rows disappear.

Do you want cells containing other months to appear empty? Use
conditional formatting, make the condition Cell Value - Is Not Equal To,
and type May in the box (or point to a cell that contains the word May).
Set the resulting format to white text, so the cell appears empty. If
the cell value is May, the format is the default for that cell.

There's probably other things you might have meant, but those are the
first two solutions I thought of.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Top