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

'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"
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

Perfect! thank you so much.

"Jim Cone" wrote:


'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"
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

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.

'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
--

====================================
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

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"
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.

'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
--

====================================
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

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