Using a macro to hide columns

[Guest]

Hi there,
I am trying to write a macro which will hide columns that do not have a "1"
in row 2. This is the code I am using, but there is something wrong with it.
Is anyone able to help?
Dim rng As range
Dim c As range
For Each c In Intersect(ActiveSheet.UsedRange,
ActiveSheet.range("a2:bd2"))
If InStr(1, c.Text, 0) > 0 Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
rng.Columns.Hidden = True

 

[Jim Cone]

'This does not hide columns if the cell is blank or
'if the cell contains text; only if the cell value is not equal to 1...

Sub MakeThemGoAway()
Dim c As Range
Dim varValue As Variant
For Each c In Application.Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Range("A2:BD2")).Cells
varValue = c.Value
If IsNumeric(varValue) Then
If varValue <> 1 Then
c.EntireColumn.Hidden = True
End If
End If
Next c
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Plum" <[email protected]>
wrote in message
Hi there,
I am trying to write a macro which will hide columns that do not have a "1"
in row 2. This is the code I am using, but there is something wrong with it.
Is anyone able to help?
Dim rng As range
Dim c As range
For Each c In Intersect(ActiveSheet.UsedRange,
ActiveSheet.range("a2:bd2"))
If InStr(1, c.Text, 0) > 0 Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
rng.Columns.Hidden = True

 

[Guest]

Perfect! thank you so much.

 

[NHRunner]

I've learned so many features of Excel that I never knew about and I guess
"Intersect" might be the next, but I don't know why or when I would use it.

The following code was given for a particular task. It realize it was code
given in response to another's question but the approach to the task seemed
at bit more complicated than it needed to be so I wondering what benefit
Intersect brought to the table.
My solution is shown below the example code.

====================================
For x = firstcol to lastcol
if isnumeric(cellvalue) then
If cellvalue <> 1 then
Columns(RtnColLet(x)).EntireColumn.Hidden = true
exit for
end if
end if
next

regards
Steve Moland

 

[Jim Cone]

Steve,

Using the Intersect method can eliminate the need to specify
starting/ending cells...
Application.Intersect(Rows(2), ActiveSheet.UsedRange)

And it can determine common areas between two ranges...
Application.Intersect(Rng1, Rng2)

It is also useful in Worksheet events to determine if a changed cell
falls within a specified range...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Me.Columns("B"), Target.Cells(1, 1)) Is Nothing Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"NHRunner" <[email protected]>
wrote in message
I've learned so many features of Excel that I never knew about and I guess
"Intersect" might be the next, but I don't know why or when I would use it.

The following code was given for a particular task. It realize it was code
given in response to another's question but the approach to the task seemed
at bit more complicated than it needed to be so I wondering what benefit
Intersect brought to the table.
My solution is shown below the example code.

====================================
For x = firstcol to lastcol
if isnumeric(cellvalue) then
If cellvalue <> 1 then
Columns(RtnColLet(x)).EntireColumn.Hidden = true
exit for
end if
end if
next

regards
Steve Moland

 

[NHRunner]

I do tons of "OnEntry" checking in applications and I'm always looking for
ways to NOT hard code stuff so this is very interesting.

<<<< Application.Intersect(Rows(2), ActiveSheet.UsedRange)

Can I assume that the inverse
Application.Intersect(Columns("B"), ActiveSheet.UsedRange)
would yield ROWS

is "UsedRange" immune from the false "right" or "bottom" that "xlbottom"
issue that doesn't seem to go away after rows/columns are cleared/deleted.

It is also useful in Worksheet events to determine if a changed cell
falls within a specified range...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Me.Columns("B"), Target.Cells(1, 1)) Is
Nothing Then

I've got only a 2 fingered grip on this one and need to run some permatation
questions of an image I'm trying to form. I can see that some of the answers
may be a "well dah" but I just want make sure.

"ExcelRange" = cell or cells touched before press of ENTER (cells perhaps
for a format change, or copy of a range.

Double negatives spin my head but how's this "If it's not nothing then
something was touched"

can I assume that "Target.cells(1,1)" means there always must be an upper
left cell even in a range of one cell.

In your example are we checking the top row in what could be a range that is
only one cell wide
And if yes, would the "1" in Target.cells(1,1) ever have a different value
If the range was more than one cell then Target.cells(2,1) could be valid.
and based on you having used Me.Columns("B") does than mean
Target.cells(1,2) would be invalid.

Is there magic in your use of "ME." as "ME" being a magic word in Excel. I
realize that your example would have ultimately come from within lots more
code where you may have just done "SET ME as (some kind of object). or
there a default ME. for object that just exist without being formerly
declared. (I think I misremember something like that in UserForms)

Thanks for wading through this.
Steve Moland

 

[Dave Peterson]

1. Me is the object that holds the code. Since the post was about a
worksheet_change event, Me is the single worksheet that owns that code.

If the code were under ThisWorkbook, then Me would refer to the workbook with
the code.

If the code were under a Userform, then Me would refer to the userform with the
code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

2. Excel.Range is the type of object that Target represents. In this case,
Target is the range that changed (single or multiple cells). And
Target.cells(1,1) is the first cell in the first area of the range that changed.

It's the same kind of thing as you'd do when you're declaring a variable:

Dim myRng as Range

(I very rarely use Excel.Range. The only time I've used that syntax is when I
was automating MSWord. MSWord has a Range object, too. And Excel.Range will
distinguish it from the MSWord's Range object.

Chip has some notes written by Alan Beban that show some other ways to address
ranges:
http://www.cpearson.com/excel/cells.htm

3. xlBottom is new to me <vbg>, but the usedrange of any sheet may be larger
than you think it should be. If that's a problem, you can use some code at
Debra Dalgleish's site to try to reset it:

http://contextures.com/xlfaqApp.html#Unused


4. If you're bothered with "not ... is nothing", you could change things
around. I like this:

If intersect(target.cells(1,1), me.range("b:b")) is nothing then
exit sub
end if

 

[Jim Cone]

Steve,

When all else fails "read the directions".

The range returned by the Intersection method is whatever Excel
interprets it to be. So it is best to specify what you want...
Set rng = Application.Intersect(rng1, rng2).Cells
Set rng = Application.Intersect(rng1, rng2).Columns
Set rng = Application.Intersect(rng1, rng2).Rows

The UsedRange size varies and is not immune from the false
right or bottom issue.

Excel Range? Not clear what you mean.

Is Nothing is either True or False, so...
Not False = True
Not True = False

The "Target" could be any size range, depending on what the user
selected or changed, so Target.Cells returns the "Cells" in the Target.
Target.Cells(1, 1) is the top left cell. (of one or more cells)

Target.Cells(2, 1) in most cases would be valid, though it could be
outside of the Target range. (think about that one).
It would be invalid if Target.Cells(2, 1) fell off of the worksheet.

I know I said read the directions, but in the case of "Me", it is not much
help. When used in the module behind (belonging to) a sheet it refers to
that sheet. When used in the module belonging to a UserForm it refers
to the Form. In most other instances, using it will throw an error.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




I do tons of "OnEntry" checking in applications and I'm always looking for
ways to NOT hard code stuff so this is very interesting.

<<<< Application.Intersect(Rows(2), ActiveSheet.UsedRange)

Can I assume that the inverse
Application.Intersect(Columns("B"), ActiveSheet.UsedRange)
would yield ROWS

is "UsedRange" immune from the false "right" or "bottom" that "xlbottom"
issue that doesn't seem to go away after rows/columns are cleared/deleted.

It is also useful in Worksheet events to determine if a changed cell
falls within a specified range...
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Me.Columns("B"), Target.Cells(1, 1)) Is
Nothing Then

I've got only a 2 fingered grip on this one and need to run some permatation
questions of an image I'm trying to form. I can see that some of the answers
may be a "well dah" but I just want make sure.

"ExcelRange" = cell or cells touched before press of ENTER (cells perhaps
for a format change, or copy of a range.

Double negatives spin my head but how's this "If it's not nothing then
something was touched"

can I assume that "Target.cells(1,1)" means there always must be an upper
left cell even in a range of one cell.

In your example are we checking the top row in what could be a range that is
only one cell wide
And if yes, would the "1" in Target.cells(1,1) ever have a different value
If the range was more than one cell then Target.cells(2,1) could be valid.
and based on you having used Me.Columns("B") does than mean
Target.cells(1,2) would be invalid.

Is there magic in your use of "ME." as "ME" being a magic word in Excel. I
realize that your example would have ultimately come from within lots more
code where you may have just done "SET ME as (some kind of object). or
there a default ME. for object that just exist without being formerly
declared. (I think I misremember something like that in UserForms)

Thanks for wading through this.
Steve Moland

 

[NHRunner]

Thanks Dave, for the clarifications.

1. Me is the object that holds the code. Since the post was about a
worksheet_change event, Me is the single worksheet that owns that code.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

That is what I thought but I've always kept my code in modules not specific
to sheets or whatever. My program origins think; data goes here,
screen/forms go here, program code here. "Here" might be separate modules
for generic functions and unique-to-the-project code but code is not spread
out in all sorts of nooks and crannies.

I can see where having "things" "own" code is logical if I was of that
persuasion, but too me it is foriegn. Not wrong, just different.

thanks again for helping.

Steve

 

[NHRunner]

Thanks for the reply Jim,

Some of my question may have seemed a bit dumb but i brought some
preconcieve notions to the party as it were.

Your answer about the cell being outside the Target.cells does make sense
but I've run into messages like "invalid object or range" so much over the
years that I could imagine that the range feeding the target.cells, defined
the available universe for the methods trying to be applied to it (the range
of the target).

I'll adjust my thinking on this one. Thanks

Steve