Excel Macro

[bmorganh]

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 Guillett]

As always, post your code for comments and suggestions.

 

[Bob Phillips]

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]

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]

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

 

[bmorganh]

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]

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]

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,

 

[Don Guillett]

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

 

[bmorganh]

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,

 

[Castell]

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