D
Dallman Ross
Dana DeLouis said:Hi. I am a little confused about where the AutoFilter should be.
Is it C:Y ??
Yes.
The reason I ask is that it appears iLastCol should not be
necessary. Note that it appears you want to Filter on C:Y, but
the loop goes from Column 1 to iLastCol (We don't know where
iLastCol is.)
I understand what you're saying (I think), but the reason I loop
from Column 1 to iLastCol is so I can combine two actions in one
macro. There were two macros originally: one cycled through all
columns looking for some we want to hide. The other cycled through
(some) columns looking for AutoFilter arrows to turn off. My
premise was, it seemed silly to call two separate macros to
cycle through columns and do Task A or Task B; I decided to
combine the macros and cycle through columns only once.
So that's why we start at 1 instead of 3 and go all the way
to iLastCol.
But then I put in an If-Statement to bother with the AutoFilter
part of the tasks (i.e., Task B above) only if iLastCol is in the
range I care about. (Then I took out the "> 3" part in testing,
because it was not doing what I expected in any case. Now I've
put that back for publishing here below.)
Btw, the VB/VBA terminology in general confuses me. Maybe
someone can explain what, exactly, a "method" is and what
exactly an "object" is. When I go to the Excel VBA Help
pages and try to read about AutoFilter, for example, I'm
asked if I want "method" or "object." I have no idea!
I wish the creators of these goofy visual languages had
stuck to normal old terminology (that I understand!)
like "expression," "statement," "action," "command," and
"assignment." Anyway, when I write my questions here,
I have been having to rein in my expressiveness to avoid
using the words I don't understand. ;-) So I've been
talking about "statement" when I've had no idea if I
should be saying "method," for example.
Here's the macro again as it is now, still with Problem 2
(iCol range acts goofy turning off AutoFilter).
As long as you're looking, please tell me if my brainstorm method
(English "method," not VBA "method" -- and I sort of am annoyed that
these newfangled visual-based languages co-opt perfectly good
English words I wish to use when talking about coding) for getting
the range thing to work ("Dim dummy as Range") is kosher, or,
if not, what I should do instead to get the #$%# code to work?
Option Explicit
Sub FilterHide()
Dim iCol As Long
Dim iLastCol As Long
Dim dummy As Range 'for AutoFilter
With Application.ActiveSheet
'// Remove AutoFilter if On
If .AutoFilterMode Then .AutoFilterMode = False
'// Turn on AutoFilter for range
Range("C1:Y1").AutoFilter
'// Find last column
iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For iCol = 1 To iLastCol
'// Turn off All Arrows between Columns 3 and 22
With .AutoFilter.Range
'// below If-statement results in some unexplained wonkiness
If iCol > 3 And iCol < 22 Then
Debug.Print iCol
.AutoFilter Field:=.Columns(iCol).Column, _
VisibleDropDown:=False
End If
End With
'// Hide columns with periwinkle interior color
If .Cells(2, iCol).Interior.ColorIndex = 24 Then
.Columns(iCol).Hidden = True
Else
.Columns(iCol).Hidden = False
End If
Next iCol
Application.ScreenUpdating = True
End With
End Sub
=====================