I have written a macro that hides several rows on a spreadsheet. The problem
is that when I insert rows above those that are to be hidden as a part of the
macro, the row numbers in Visual Basic do not change, thereby the incorrect
rows are hidden.

As always, post your code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Don,

Here is the portion of the macro that addresses the hiding of the rows.
" Sheets("Material and Services").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("23:27").Select
Selection.EntireRow.Hidden = True
Range("A8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True"

I would like to have the row numbers "23:27" increase or decrease
automatically when I add or delete rows on the spreadsheet above row 23.

thanks,

"Don Guillett" wrote:

As always, post your code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

This will hide/unhide from anywhere in the workbook withOUT selections.
Assumes you did mean from row 23 down to and including the last row.???

Sub hiderows()
With Sheets("Material and Services")
.Unprotect
lr = .Cells(Rows.Count, "a").End(xlUp).Row
If .Rows("23").Hidden = True Then
.Rows.Hidden = False
Else
.Rows("23:" & lr).Hidden = True
End If
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
Don,

Here is the portion of the macro that addresses the hiding of the rows.
" Sheets("Material and Services").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("23:27").Select
Selection.EntireRow.Hidden = True
Range("A8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True"

I would like to have the row numbers "23:27" increase or decrease
automatically when I add or delete rows on the spreadsheet above row 23.

thanks,

"Don Guillett" wrote:

As always, post your code for comments and suggestions.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part
of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Bob,

Naming the rows seems to work well when I perform a functions such are
changing the font color, etc. but when I try to use a name for hiding rows
the macro seems to automatically override the name and always hide the
original row numbers as if they are absolute row numbers. Therein lies the
problem, when I insert rows above the macro should follow the named cells,
but it only hides the original rows and disregards the name.

thanks,

"Bob Phillips" wrote:

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

That seems odd. Can you post the code?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"bmorganh" wrote in message
...
Bob,

Naming the rows seems to work well when I perform a functions such are
changing the font color, etc. but when I try to use a name for hiding
rows
the macro seems to automatically override the name and always hide the
original row numbers as if they are absolute row numbers. Therein lies
the
problem, when I insert rows above the macro should follow the named cells,
but it only hides the original rows and disregards the name.

thanks,

"Bob Phillips" wrote:

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part
of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Bob,

Here is the portion of the macro that addresses the hiding of the rows.
" Sheets("Material and Services").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("23:27").Select
Selection.EntireRow.Hidden = True
Range("A8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True"

I would like to have the row numbers "23:27" increase or decrease
automatically inside the macro when I add or delete rows on the spreadsheet
above row 23.

thanks,


"Bob Phillips" wrote:

That seems odd. Can you post the code?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"bmorganh" wrote in message
...
Bob,

Naming the rows seems to work well when I perform a functions such are
changing the font color, etc. but when I try to use a name for hiding
rows
the macro seems to automatically override the name and always hide the
original row numbers as if they are absolute row numbers. Therein lies
the
problem, when I insert rows above the macro should follow the named cells,
but it only hides the original rows and disregards the name.

thanks,

"Bob Phillips" wrote:

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a part
of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Did you look at my offering which acts on all rows from 23 to the bottom
assuming col A as the longest column?
If you have 23:27 and delete 25 you will now have 23:26. If you add a row at
25 you will have 23:28 hidden,etc.
You haven't been very clear. Is this what you want?

This will hide/unhide from anywhere in the workbook withOUT selections.
Assumes you did mean from row 23 down to and including the last row.???

Sub hiderows()
With Sheets("Material and Services")
.Unprotect
.columns.hidden=false ' added today
lr = .Cells(Rows.Count, "a").End(xlUp).Row
If .Rows("23").Hidden = True Then
.Rows.Hidden = False
Else
.Rows("23:" & lr).Hidden = True
End If
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
Bob,

Here is the portion of the macro that addresses the hiding of the rows.
" Sheets("Material and Services").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("23:27").Select
Selection.EntireRow.Hidden = True
Range("A8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True"

I would like to have the row numbers "23:27" increase or decrease
automatically inside the macro when I add or delete rows on the
spreadsheet
above row 23.

thanks,


"Bob Phillips" wrote:

That seems odd. Can you post the code?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
Bob,

Naming the rows seems to work well when I perform a functions such are
changing the font color, etc. but when I try to use a name for hiding
rows
the macro seems to automatically override the name and always hide the
original row numbers as if they are absolute row numbers. Therein lies
the
problem, when I insert rows above the macro should follow the named
cells,
but it only hides the original rows and disregards the name.

thanks,

"Bob Phillips" wrote:

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a
part
of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Don/Bob,

I'm sorry that I haven't been too clear. Let me try again.

At the bottom of group of rows that contain data I have several rows that
perform certain math functions upon the data in the rows above them. The
data rows are variable, some times there are only a few rows, but other times
I have many data rows.

Some contracts require that the summation functions are visible and other
contracts require that he summation functions are hidden.

What I would like to do is be able to insert and delete data rows with out
affecting the ability of a macro to hide or unhide the summation function
rows.

thank you,


"Don Guillett" wrote:

Did you look at my offering which acts on all rows from 23 to the bottom
assuming col A as the longest column?
If you have 23:27 and delete 25 you will now have 23:26. If you add a row at
25 you will have 23:28 hidden,etc.
You haven't been very clear. Is this what you want?

This will hide/unhide from anywhere in the workbook withOUT selections.
Assumes you did mean from row 23 down to and including the last row.???

Sub hiderows()
With Sheets("Material and Services")
.Unprotect
.columns.hidden=false ' added today
lr = .Cells(Rows.Count, "a").End(xlUp).Row
If .Rows("23").Hidden = True Then
.Rows.Hidden = False
Else
.Rows("23:" & lr).Hidden = True
End If
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
Bob,

Here is the portion of the macro that addresses the hiding of the rows.
" Sheets("Material and Services").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("23:27").Select
Selection.EntireRow.Hidden = True
Range("A8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True"

I would like to have the row numbers "23:27" increase or decrease
automatically inside the macro when I add or delete rows on the
spreadsheet
above row 23.

thanks,


"Bob Phillips" wrote:

That seems odd. Can you post the code?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
Bob,

Naming the rows seems to work well when I perform a functions such are
changing the font color, etc. but when I try to use a name for hiding
rows
the macro seems to automatically override the name and always hide the
original row numbers as if they are absolute row numbers. Therein lies
the
problem, when I insert rows above the macro should follow the named
cells,
but it only hides the original rows and disregards the name.

thanks,

"Bob Phillips" wrote:

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet. The
problem
is that when I insert rows above those that are to be hidden as a
part
of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.

Your code still refers to row numbers rather than the named range.


"bmorganh" wrote in message
...
Don/Bob,

I'm sorry that I haven't been too clear. Let me try again.

At the bottom of group of rows that contain data I have several rows that
perform certain math functions upon the data in the rows above them. The
data rows are variable, some times there are only a few rows, but other
times
I have many data rows.

Some contracts require that the summation functions are visible and other
contracts require that he summation functions are hidden.

What I would like to do is be able to insert and delete data rows with out
affecting the ability of a macro to hide or unhide the summation function
rows.

thank you,


"Don Guillett" wrote:

Did you look at my offering which acts on all rows from 23 to the bottom
assuming col A as the longest column?
If you have 23:27 and delete 25 you will now have 23:26. If you add a row
at
25 you will have 23:28 hidden,etc.
You haven't been very clear. Is this what you want?

This will hide/unhide from anywhere in the workbook withOUT selections.
Assumes you did mean from row 23 down to and including the last row.???

Sub hiderows()
With Sheets("Material and Services")
.Unprotect
.columns.hidden=false ' added today
lr = .Cells(Rows.Count, "a").End(xlUp).Row
If .Rows("23").Hidden = True Then
.Rows.Hidden = False
Else
.Rows("23:" & lr).Hidden = True
End If
.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End With
End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"bmorganh" wrote in message
...
Bob,

Here is the portion of the macro that addresses the hiding of the rows.
" Sheets("Material and Services").Select
ActiveSheet.Unprotect
Cells.Select
Selection.EntireColumn.Hidden = False
Rows("23:27").Select
Selection.EntireRow.Hidden = True
Range("A8").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True"

I would like to have the row numbers "23:27" increase or decrease
automatically inside the macro when I add or delete rows on the
spreadsheet
above row 23.

thanks,


"Bob Phillips" wrote:

That seems odd. Can you post the code?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"bmorganh" wrote in message
...
Bob,

Naming the rows seems to work well when I perform a functions such
are
changing the font color, etc. but when I try to use a name for
hiding
rows
the macro seems to automatically override the name and always hide
the
original row numbers as if they are absolute row numbers. Therein
lies
the
problem, when I insert rows above the macro should follow the named
cells,
but it only hides the original rows and disregards the name.

thanks,

"Bob Phillips" wrote:

Name the rows and use the range names in your code.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in
my
addy)



"bmorganh" wrote in message
...
I have written a macro that hides several rows on a spreadsheet.
The
problem
is that when I insert rows above those that are to be hidden as a
part
of
the
macro, the row numbers in Visual Basic do not change, thereby the
incorrect
rows are hidden.