Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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"? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to Hide columns in spreadsheet but NOT hide data in chart. | Charts and Charting in Excel | |||
Hide/Unhide columns using button on top over relevant columns | Excel Discussion (Misc queries) | |||
hide a worksheet so that a macro can still find it | Excel Worksheet Functions | |||
Excel button :: Filter columns by value - possible? Additionally, hide certain columns | Excel Programming | |||
find and delete duplicate entries in two columns or find and prin. | Excel Programming |