Autoadjust range declaration coding when inserting rows
 

Is there a way to auto-adjust the range settings for when you add/delete rows
to an Excel sheet? I'm trying to set up an easy way for end-users to
show/hide blocks of information with a button, and I'm using the code below
for each button:

Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("7:12")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

Now, the problem is that these blocks of info will have extra lines
inserted, which tends to break all of the coding. Is there some sort of
adjustment to the coding that I can make to accomodate for that situation, or
would the best way be to name each group of rows, and have the range set to
the names, letting Excel auto-adjust the name ranges and keeping the code
from breaking that way?

Autoadjust range declaration coding when inserting rows
 

What happens when some rows in the range are hidden and some are not? I
suspect you'll get an error on the line (although I've not tested it).

Barb Reinhardt



"JW73" wrote:

Is there a way to auto-adjust the range settings for when you add/delete rows
to an Excel sheet? I'm trying to set up an easy way for end-users to
show/hide blocks of information with a button, and I'm using the code below
for each button:

Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("7:12")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

Now, the problem is that these blocks of info will have extra lines
inserted, which tends to break all of the coding. Is there some sort of
adjustment to the coding that I can make to accomodate for that situation, or
would the best way be to name each group of rows, and have the range set to
the names, letting Excel auto-adjust the name ranges and keeping the code
from breaking that way?

Autoadjust range declaration coding when inserting rows
 

Range naming is probably the best solution (although not fool proof). You can
write code that will find last row in a data region etc but I wouldn't
endorse it, especially in a shared workbook.

People familiar with database record handling experience invariably cringe
when it comes to managing new items in an excel 'table'.
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Barb Reinhardt" wrote:

What happens when some rows in the range are hidden and some are not? I
suspect you'll get an error on the line (although I've not tested it).

Barb Reinhardt



"JW73" wrote:

Is there a way to auto-adjust the range settings for when you add/delete rows
to an Excel sheet? I'm trying to set up an easy way for end-users to
show/hide blocks of information with a button, and I'm using the code below
for each button:

Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("7:12")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

Now, the problem is that these blocks of info will have extra lines
inserted, which tends to break all of the coding. Is there some sort of
adjustment to the coding that I can make to accomodate for that situation, or
would the best way be to name each group of rows, and have the range set to
the names, letting Excel auto-adjust the name ranges and keeping the code
from breaking that way?

Autoadjust range declaration coding when inserting rows
 

Thanks - I was working on possible solutions all night, and based on the way
the form is set up, and who will be using it, decided that that would
probably have the least chance of breaking.

"K_Macd" wrote:

Range naming is probably the best solution (although not fool proof). You can
write code that will find last row in a data region etc but I wouldn't
endorse it, especially in a shared workbook.

People familiar with database record handling experience invariably cringe
when it comes to managing new items in an excel 'table'.
--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"


"Barb Reinhardt" wrote:

What happens when some rows in the range are hidden and some are not? I
suspect you'll get an error on the line (although I've not tested it).

Barb Reinhardt



"JW73" wrote:

Is there a way to auto-adjust the range settings for when you add/delete rows
to an Excel sheet? I'm trying to set up an easy way for end-users to
show/hide blocks of information with a button, and I'm using the code below
for each button:

Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("7:12")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

Now, the problem is that these blocks of info will have extra lines
inserted, which tends to break all of the coding. Is there some sort of
adjustment to the coding that I can make to accomodate for that situation, or
would the best way be to name each group of rows, and have the range set to
the names, letting Excel auto-adjust the name ranges and keeping the code
from breaking that way?

Autoadjust range declaration coding when inserting rows
 

Nope - if some of the lines are visible, it hides the whole group upon click

"Barb Reinhardt" wrote:

What happens when some rows in the range are hidden and some are not? I
suspect you'll get an error on the line (although I've not tested it).

Barb Reinhardt



"JW73" wrote:

Is there a way to auto-adjust the range settings for when you add/delete rows
to an Excel sheet? I'm trying to set up an easy way for end-users to
show/hide blocks of information with a button, and I'm using the code below
for each button:

Private Sub CommandButton1_Click()
Dim myRng As Range
Set myRng = Me.Range("7:12")
myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
End Sub

Now, the problem is that these blocks of info will have extra lines
inserted, which tends to break all of the coding. Is there some sort of
adjustment to the coding that I can make to accomodate for that situation, or
would the best way be to name each group of rows, and have the range set to
the names, letting Excel auto-adjust the name ranges and keeping the code
from breaking that way?