hide columns from find
 

How do you tell XL to hide all columns in the active worksheet from a
cell where you found text, rFoundCell (declared as a range) to column
"Z"?

hide columns from find
 

Worksheet("MySheet").Range(Columns(col), Columns(26)).Hidden = True

where col = rFoundCell.column
--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...
How do you tell XL to hide all columns in the active worksheet from a
cell where you found text, rFoundCell (declared as a range) to column
"Z"?

hide columns from find
 

If you need to qualify Range, you need to qualify columns.

With Worksheet("MySheet")
.Range(.Columns(col), .Columns(26)).Hidden = True
End With

--
Regards,
Tom Ogilvy


"STEVE BELL" wrote in message
news:kSwAe.3015$Gk4.2169@trnddc01...
Worksheet("MySheet").Range(Columns(col), Columns(26)).Hidden = True

where col = rFoundCell.column
--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...
How do you tell XL to hide all columns in the active worksheet from a
cell where you found text, rFoundCell (declared as a range) to column
"Z"?

hide columns from find
 

That worked, Steve. But I can't figure out why the rest of the macro
isn't incrementing. The macro runs, but just on the currently selected
sheet.

Sub HideCol()
Dim rTopCell As Range
Dim lColTop As Long
Dim WkSht As Worksheet
For Each WkSht In ActiveWorkbook.Worksheets
If Not Right(WkSht.Name, 7) = "Records" Then

Set rTopCell = ActiveSheet.Range("3:3").find("top",
LookIn:=xlValues, LookAt:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column

WkSht.Range(Columns(lColTop), Columns("AC")).Hidden =
True
End If
End If
Next
End Sub

STEVE BELL wrote:
Worksheet("MySheet").Range(Columns(col), Columns(26)).Hidden = True

where col = rFoundCell.column
--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...
How do you tell XL to hide all columns in the active worksheet from a
cell where you found text, rFoundCell (declared as a range) to column
"Z"?

hide columns from find
 

Sub HideCol()
Dim rTopCell As Range
Dim lColTop As Long
Dim WkSht As Worksheet
For Each WkSht In ActiveWorkbook.Worksheets
If Not Right(WkSht.Name, 7) = "Records" Then

Set rTopCell = WkSht.Range("3:3").find("top", _
LookIn:=xlValues, LookAt:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column

WkSht.Range( WkSht.Columns(lColTop), _
WkSht.Columns("AC")).Hidden =True
End If
End If
Next
End Sub


--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...
That worked, Steve. But I can't figure out why the rest of the macro
isn't incrementing. The macro runs, but just on the currently selected
sheet.

Sub HideCol()
Dim rTopCell As Range
Dim lColTop As Long
Dim WkSht As Worksheet
For Each WkSht In ActiveWorkbook.Worksheets
If Not Right(WkSht.Name, 7) = "Records" Then

Set rTopCell = ActiveSheet.Range("3:3").find("top",
LookIn:=xlValues, LookAt:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column

WkSht.Range(Columns(lColTop), Columns("AC")).Hidden =
True
End If
End If
Next
End Sub

STEVE BELL wrote:
Worksheet("MySheet").Range(Columns(col), Columns(26)).Hidden = True

where col = rFoundCell.column
--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...
How do you tell XL to hide all columns in the active worksheet from a
cell where you found text, rFoundCell (declared as a range) to column
"Z"?

hide columns from find
 

I did some more testing. When I run the macro on other sheets in the
workbook (the ones the macro can't find when run from the first sheet),
I get "Range method of object failed" on

WkSht.Range(Columns(lColTop), Columns("AC")).Hidden =
True

I watched the values for both lColTop and the found cell and both are
correct. Any ideas?
Thanks for the help.

hide columns from find
 

Guess you need to read everybody's response.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
ups.com...
I did some more testing. When I run the macro on other sheets in the
workbook (the ones the macro can't find when run from the first sheet),
I get "Range method of object failed" on

WkSht.Range(Columns(lColTop), Columns("AC")).Hidden =
True

I watched the values for both lColTop and the found cell and both are
correct. Any ideas?
Thanks for the help.

hide columns from find
 

Thanks again, Tom (sorry I called you "Steve" in last reply). Works
fine, as usual!

Tom Ogilvy wrote:
Sub HideCol()
Dim rTopCell As Range
Dim lColTop As Long
Dim WkSht As Worksheet
For Each WkSht In ActiveWorkbook.Worksheets
If Not Right(WkSht.Name, 7) = "Records" Then

Set rTopCell = WkSht.Range("3:3").find("top", _
LookIn:=xlValues, LookAt:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column

WkSht.Range( WkSht.Columns(lColTop), _
WkSht.Columns("AC")).Hidden =True
End If
End If
Next
End Sub


--
Regards,
Tom Ogilvy

"davegb" wrote in message
oups.com...
That worked, Steve. But I can't figure out why the rest of the macro
isn't incrementing. The macro runs, but just on the currently selected
sheet.

Sub HideCol()
Dim rTopCell As Range
Dim lColTop As Long
Dim WkSht As Worksheet
For Each WkSht In ActiveWorkbook.Worksheets
If Not Right(WkSht.Name, 7) = "Records" Then

Set rTopCell = ActiveSheet.Range("3:3").find("top",
LookIn:=xlValues, LookAt:=xlPart)
If Not rTopCell Is Nothing Then
lColTop = rTopCell.Column

WkSht.Range(Columns(lColTop), Columns("AC")).Hidden =
True
End If
End If
Next
End Sub

STEVE BELL wrote:
Worksheet("MySheet").Range(Columns(col), Columns(26)).Hidden = True

where col = rFoundCell.column
--
steveB

Remove "AYN" from email to respond
"davegb" wrote in message
oups.com...
How do you tell XL to hide all columns in the active worksheet from a
cell where you found text, rFoundCell (declared as a range) to column
"Z"?

hide columns from find
 

Apparently yours posted while I was working on my response to Steve.


Tom Ogilvy wrote:
Guess you need to read everybody's response.

--
Regards,
Tom Ogilvy


"davegb" wrote in message
ups.com...
I did some more testing. When I run the macro on other sheets in the
workbook (the ones the macro can't find when run from the first sheet),
I get "Range method of object failed" on

WkSht.Range(Columns(lColTop), Columns("AC")).Hidden =
True

I watched the values for both lColTop and the found cell and both are
correct. Any ideas?
Thanks for the help.