Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
I'm working on a set of worksheets in a workbook where I want to use a
macro to Hide certain columns before I protect them send them out to end users. The starting column that I want to hide varies, depending on how many columns of data there are in that particular sheet. On every sheet, however, the columns I want to hide start with one that has the text "Top 10" in the third row. The hidden columns end at Column AC in all sheets. My current thinking is to write a macro that selects row 3 and finds the work "Top". Set the column as a variable "TopCol", then have XL hide all columns from that column to Column AC. But I can't figure out how to tell XL I want to select all columns from a variable, TopCol, to a constant "AC". I know how to do ("AA:AC"), but what is the syntax when one of the column identifiers is a variable, the other the normal alpha label? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Dave,
This assumes you have the code to find your variable column: Sub test() Dim top_col As Range Dim cols_to_hide As Range Set top_col = Columns("AA") 'code to determine top_col will replace this Set cols_to_hide = Range(top_col, Columns("AC")) cols_to_hide.Hidden = True End Sub hth, Doug "davegb" wrote in message oups.com... I'm working on a set of worksheets in a workbook where I want to use a macro to Hide certain columns before I protect them send them out to end users. The starting column that I want to hide varies, depending on how many columns of data there are in that particular sheet. On every sheet, however, the columns I want to hide start with one that has the text "Top 10" in the third row. The hidden columns end at Column AC in all sheets. My current thinking is to write a macro that selects row 3 and finds the work "Top". Set the column as a variable "TopCol", then have XL hide all columns from that column to Column AC. But I can't figure out how to tell XL I want to select all columns from a variable, TopCol, to a constant "AC". I know how to do ("AA:AC"), but what is the syntax when one of the column identifiers is a variable, the other the normal alpha label? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
I tried running your code with mine, but there's a problem with my code
being based on finding a cell, not a range, and your code looking for a range input. At this point, it looks like: Sub Macro1() Dim TopCol As Range Dim Cols2Hide As Range Rows("3:3").Select Selection.Find(What:="top", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Set TopCol = ActiveCell.Column Set Cols2Hide = Range(TopCol, Columns("AC")) Cols2Hide.Hidden = True End Sub I'm getting a type mismatch at "Set TopCol=ActiveCell.column". I understand why, just don't know how to fix it. Never do! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Dave,
This works for me: Sub test() Dim top_cell As Range Dim TopCol As Range Dim Cols2Hide As Range Set top_cell = Rows(3).Find(What:="top", LookIn:=xlValues) Set TopCol = Columns(top_cell.Column) Set Cols2Hide = Range(TopCol, Columns("AC")) Cols2Hide.Hidden = True End Sub Doug "davegb" wrote in message oups.com... I tried running your code with mine, but there's a problem with my code being based on finding a cell, not a range, and your code looking for a range input. At this point, it looks like: Sub Macro1() Dim TopCol As Range Dim Cols2Hide As Range Rows("3:3").Select Selection.Find(What:="top", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False).Activate Set TopCol = ActiveCell.Column Set Cols2Hide = Range(TopCol, Columns("AC")) Cols2Hide.Hidden = True End Sub I'm getting a type mismatch at "Set TopCol=ActiveCell.column". I understand why, just don't know how to fix it. Never do! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Worked great, Doug. Thanks!
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
I've been modifying the code Doug gave me to hide the columns in every
worksheet in the workbook. But I'm getting an error, "Object variable or with block variable not set" when I run the following: Sub AllSheetsColHide() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Object For Each wkSht In ActiveWorkbook.Worksheets Set TopCell = Rows(3).Find(What:="top", LookIn:=xlValues) Set TopCol = Columns(TopCell.Column) <------------------------[error here] Set Cols2Hide = Range(TopCol, Columns("AC")) Cols2Hide.Hidden = True Next wkSht End Sub Any suggestions? Thanks in advance! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Dave,
Try this. It qualifies the ranges each time through with "wksht" otherwise you would be referring to the first sheet each time. I also changed the "wksht" declaration from "Object" to "Worksheet." Finally, I added a test to make test whether it found "top" in row 3, if not it skips the next steps.: Sub AllSheetsColHide() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) If Not TopCell Is Nothing Then ' if it found "top" Set TopCol = .Columns(TopCell.Column) Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True End If End With Next wkSht End Sub hth, Doug "davegb" wrote in message oups.com... I've been modifying the code Doug gave me to hide the columns in every worksheet in the workbook. But I'm getting an error, "Object variable or with block variable not set" when I run the following: Sub AllSheetsColHide() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Object For Each wkSht In ActiveWorkbook.Worksheets Set TopCell = Rows(3).Find(What:="top", LookIn:=xlValues) Set TopCol = Columns(TopCell.Column) <------------------------[error here] Set Cols2Hide = Range(TopCol, Columns("AC")) Cols2Hide.Hidden = True Next wkSht End Sub Any suggestions? Thanks in advance! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Thanks, Doug, it works. But I'm even more confused, if that's possible!
I modified my code bases on yours trying to figure out what was wrong with mine. I left out the if statement because right now it isn't needed. But I still get the same error in the same place! I ended up with: Sub AllSheetsColHide() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) Set TopCol = .Columns(TopCell.Column) <----------------------[object variable or block variable not set] Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True End With Next wkSht End Sub Why does this not run? It's exactly the same as yours without the If statement. Also, you wrote: It qualifies the ranges each time through with "wksht" otherwise you would be referring to the first sheet each time. What does this mean? How do you "qualify a range"? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Dave,
You say the If statement is not needed now, but does it work with the If statement in? The way you've got it set up, the module loops through each worksheet in the workbook and searches for the value "top" in the 3rd row. If it doesn't find it in a given sheet then TopCell will be null and setting TopCol will cause the error you're seeing. The If test I put in checks whether TopCell has a value, i.e., the Find Function found "top" on Row3 of that sheet. If it didn't then the If statement skips the rest of the code, thereby skipping the error you're getting. So again, does it work with the If statement. If not, does it error the first time through the loop or on later sheets? Doug "davegb" wrote in message ups.com... Thanks, Doug, it works. But I'm even more confused, if that's possible! I modified my code bases on yours trying to figure out what was wrong with mine. I left out the if statement because right now it isn't needed. But I still get the same error in the same place! I ended up with: Sub AllSheetsColHide() 'for all sheets in currently active workbook, assigned to button Dim TopCell As Range Dim TopCol As Range Dim Cols2Hide As Range Dim wkSht As Worksheet For Each wkSht In ActiveWorkbook.Worksheets With wkSht Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues) Set TopCol = .Columns(TopCell.Column) <----------------------[object variable or block variable not set] Set Cols2Hide = .Range(TopCol, .Columns("AC")) Cols2Hide.Hidden = True End With Next wkSht End Sub Why does this not run? It's exactly the same as yours without the If statement. Also, you wrote: It qualifies the ranges each time through with "wksht" otherwise you would be referring to the first sheet each time. What does this mean? How do you "qualify a range"? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Doug, thanks for your reply.
It works with the IF statement, and without it, errors on the first try. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Dave,
So, leave the If statement in and you should be good, since it won't do anything on sheets without "top" in row 3. If there's something I'm not understanding this let me know. hth, Doug "davegb" wrote in message oups.com... Doug, thanks for your reply. It works with the IF statement, and without it, errors on the first try. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo column identifier
Thanks, Doug.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Row and Column identifier tabs now showing on sheet 1 | Setting up and Configuration of Excel | |||
Combo Stacked column/column/line | Charts and Charting in Excel | |||
Adding a unique identifier to a column of data | Excel Worksheet Functions | |||
Unique identifier | Excel Discussion (Misc queries) | |||
Stacked column and column combo chart | Charts and Charting in Excel |