Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide or protect hidden rows/columns - macro help
I have financial statements that have hidden rows and columns. If they are
hidden, it means that there is either a 0 suppress action or the values are blank. However, when the user selects data from the report, it still selects the hidden rows/columns. I want to put a macro in that "finds" these hidden rows/columns, and then locks them. The problem is that the rows and columns that are hidden aren't always the same, so I need a macro that can handle the dynamic change, therefore the attribute of a hidden row/column/cell is the most valuable. Is there a way to do this? Once the cells/rows/columns are locked, I will protect the those elements from being selected and Protect that range with a password. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide or protect hidden rows/columns - macro help
Dim cell as Range, rng as Range
for each cell in Activesheet.UsedRange.columns(1).cells if cell.entireRow.Hidden then if rng is nothing then set rng = cell.Entirerow else set rng = Union(rng, cell.EntireRow) end if end if Next if not rng1 is nothing then rng.Locked = True . . . -- Regards, Tom Ogilvy "ttbbgg" wrote: I have financial statements that have hidden rows and columns. If they are hidden, it means that there is either a 0 suppress action or the values are blank. However, when the user selects data from the report, it still selects the hidden rows/columns. I want to put a macro in that "finds" these hidden rows/columns, and then locks them. The problem is that the rows and columns that are hidden aren't always the same, so I need a macro that can handle the dynamic change, therefore the attribute of a hidden row/column/cell is the most valuable. Is there a way to do this? Once the cells/rows/columns are locked, I will protect the those elements from being selected and Protect that range with a password. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide or protect hidden rows/columns - macro help
Having trouble deciphering this, however just simply locking the cells is not
going to be enough. I need to delete the row entirely if it is marked as hidden. The same goes if a column is hidden. The report is downloaded from a system that used hidden rows and columns for calculations on the template. Once downloaded into Excel, I want a clean sheet where the user can freely select the data that is there and not have to worry about selecting hidden data. "Tom Ogilvy" wrote: Dim cell as Range, rng as Range for each cell in Activesheet.UsedRange.columns(1).cells if cell.entireRow.Hidden then if rng is nothing then set rng = cell.Entirerow else set rng = Union(rng, cell.EntireRow) end if end if Next if not rng1 is nothing then rng.Locked = True . . . -- Regards, Tom Ogilvy "ttbbgg" wrote: I have financial statements that have hidden rows and columns. If they are hidden, it means that there is either a 0 suppress action or the values are blank. However, when the user selects data from the report, it still selects the hidden rows/columns. I want to put a macro in that "finds" these hidden rows/columns, and then locks them. The problem is that the rows and columns that are hidden aren't always the same, so I need a macro that can handle the dynamic change, therefore the attribute of a hidden row/column/cell is the most valuable. Is there a way to do this? Once the cells/rows/columns are locked, I will protect the those elements from being selected and Protect that range with a password. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide or protect hidden rows/columns - macro help
I want to put a macro in that "finds" these hidden
rows/columns, and then locks them. just simply locking the cells is not going to be enough. -- Regards, Tom Ogilvy "ttbbgg" wrote in message ... Having trouble deciphering this, however just simply locking the cells is not going to be enough. I need to delete the row entirely if it is marked as hidden. The same goes if a column is hidden. The report is downloaded from a system that used hidden rows and columns for calculations on the template. Once downloaded into Excel, I want a clean sheet where the user can freely select the data that is there and not have to worry about selecting hidden data. "Tom Ogilvy" wrote: Dim cell as Range, rng as Range for each cell in Activesheet.UsedRange.columns(1).cells if cell.entireRow.Hidden then if rng is nothing then set rng = cell.Entirerow else set rng = Union(rng, cell.EntireRow) end if end if Next if not rng1 is nothing then rng.Locked = True . . . -- Regards, Tom Ogilvy "ttbbgg" wrote: I have financial statements that have hidden rows and columns. If they are hidden, it means that there is either a 0 suppress action or the values are blank. However, when the user selects data from the report, it still selects the hidden rows/columns. I want to put a macro in that "finds" these hidden rows/columns, and then locks them. The problem is that the rows and columns that are hidden aren't always the same, so I need a macro that can handle the dynamic change, therefore the attribute of a hidden row/column/cell is the most valuable. Is there a way to do this? Once the cells/rows/columns are locked, I will protect the those elements from being selected and Protect that range with a password. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide or protect hidden rows/columns - macro help
Tom;
Agreed, I now know that I need the entire hidden row and column deleted from the worksheet. I have been using the following macro with some success but incomplete. The problem is, it only deletes the cell, I need the entire row deleted and then the column deleted (consecutively). Sub FindHiddenAndLock() Dim mcell, MyRange As Range Set MyRange = Range("A1:A10") For Each mcell In MyRange If mcell.Rows.Hidden = True Then mcell.Rows.Delete End If Next End Sub "Tom Ogilvy" wrote: I want to put a macro in that "finds" these hidden rows/columns, and then locks them. just simply locking the cells is not going to be enough. -- Regards, Tom Ogilvy "ttbbgg" wrote in message ... Having trouble deciphering this, however just simply locking the cells is not going to be enough. I need to delete the row entirely if it is marked as hidden. The same goes if a column is hidden. The report is downloaded from a system that used hidden rows and columns for calculations on the template. Once downloaded into Excel, I want a clean sheet where the user can freely select the data that is there and not have to worry about selecting hidden data. "Tom Ogilvy" wrote: Dim cell as Range, rng as Range for each cell in Activesheet.UsedRange.columns(1).cells if cell.entireRow.Hidden then if rng is nothing then set rng = cell.Entirerow else set rng = Union(rng, cell.EntireRow) end if end if Next if not rng1 is nothing then rng.Locked = True . . . -- Regards, Tom Ogilvy "ttbbgg" wrote: I have financial statements that have hidden rows and columns. If they are hidden, it means that there is either a 0 suppress action or the values are blank. However, when the user selects data from the report, it still selects the hidden rows/columns. I want to put a macro in that "finds" these hidden rows/columns, and then locks them. The problem is that the rows and columns that are hidden aren't always the same, so I need a macro that can handle the dynamic change, therefore the attribute of a hidden row/column/cell is the most valuable. Is there a way to do this? Once the cells/rows/columns are locked, I will protect the those elements from being selected and Protect that range with a password. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Protect Hidden Columns and Rows | Excel Discussion (Misc queries) | |||
Macro that will hidden columns, rows and worksheets based on an identifier | Excel Discussion (Misc queries) | |||
Protect hidden rows/columns -macro help | Excel Discussion (Misc queries) | |||
Macro to hide/show rows and columns | Excel Discussion (Misc queries) |