Syntax needed for With

[count]

Hi,
Task at hand is to hide those columns, whose headings are on the Named Range
"Elements" list (stored aside)
I used to do it via plain looping but I saw recently clever usage of With
construct.
I would like to learn how to put With into use and start using objects more.
TIA
Paul

 

[Bob Phillips]

Hi Paul,

There is no need for With this requirement.

There is nothing clever about With, it is just used to remove duplicated
object qualifying, thereby making the code more efficient as VBA does not
need to look up the reference each time, the With stores that reference in
memory, and makes the code easier to read IMO.

Here is the code you want

Dim col As Range

For Each col In ActiveSheet.Range("Elements").Columns
col.Hidden = True
Next col


--

HTH

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

 

[Frank Kabel]

Hi Paul
with is usually used to avoid typing the object reference over and over
again. So kind of reduced typing. to give you an example
1. Without using With:
sub foo_1()
Dim wks as worksheet
dim rng as worksheet

set wks = activesheet
set rng = wks.range("A1:A100")
rng.value = 1
rng.offset(0,1).value = 2
rng.offset(0,2).value = 3
wks.printout
end sub

2. Using with
sub foo_2()
Dim wks as worksheet
dim rng as worksheet

set wks = activesheet
with wks
set rng = .range("A1:A100")
with rng
.value = 1
.offset(0,1).value = 2
.offset(0,2).value = 3
end with
.printout
end with
end sub

 

[count]

Ooops,
I haven't make myself clear enuf
NamedRange "Elements" is vertical 1 column list of interesting headings
Sheet to treat has oodles more columns - only some will get hidden - those
that ... you know.
thanks for your sooo quick response
Paul

 

[count]

Frank,
thanks - I got it!
Your example is clear to follow. I'll start using ranges technique instead
of looping through cells.
Danke
Paul