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.

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.

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.

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.

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.