Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Also, here is my answer to your explanation in the original thread:
Assume you have a sheet (named Data) with 37 cells in column B containing an X if you want a column hidden. If I want column B hidden, I put an X in B2, if I want column AA hidden, I put an X in B27. You can have the 37 labels in column A, but that is not needed by the macro. Assume this sheet is named "data" and the only other sheets in the workbook are the 7 sheets. Sub HandleColumns() Dim varr() Dim sStr As String, rng As Range Dim sh As Worksheet On Error Resume Next With Worksheets("Data") Set rng = .Range(.Cells(1, 2), .Cells(37, 2)). _ SpecialCells(xlConstants, xlTextValues) End With On Error GoTo 0 if rng is nothing then exit sub ReDim varr(1 To rng.Count) sStr = "" For Each cell In rng sCol = Left(Cells(1, cell.Row).Address(0, 0), 2 + (cell.Row < 27)) sStr = sStr & sCol & ":" & sCol & "," Next sStr = Left(sStr, Len(sStr) - 1) If Len(Trim(sStr)) = 0 Then Exit Sub For Each sh In Worksheets If LCase(sh.Name) < "data" Then sh.Columns.Hidden = False sh.Range(sStr).EntireColumn.Hidden = True End If Next End Sub -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim sh as Worksheet, sCol as String sCol = Trim(Worksheets("Sheet1").Range("N42").Value) for each sh in ActiveWindow.SelectedSheets sh.Columns(sCol & ":" & sCol).Hidden = True Next -- Regards, Tom Ogilvy "Neal" wrote in message ... I have a macro to hide a column in an array of worksheets. I would like to have it get the column from a cell in a worksheet. How do I go about doing that? In other words, If cell N42 has a J in it, then the J would be transferred to the macro. Dim sh As Worksheet For Each sh In ActiveWindow.SelectedSheets sh.Columns("g:g").Hidden = True Next Thanks Neal |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Passing data to fix cell | Excel Worksheet Functions | |||
Passing variables between a form and macro | New Users to Excel | |||
Passing variables between a form and macro | New Users to Excel | |||
passing arguments from an excel macro to a word macro | Excel Discussion (Misc queries) | |||
Passing Text from User Form to Spreadsheet | Excel Programming |