"locking" links
 

i just made a bunch of little macros to hide/unhide things and noticed that
when i insert cells above the cells that are written in my macros, it now
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...

"locking" links
 

Without seeing your code it is hard to say but if you hard-coded the cell
references you will get incorrect ranges after inserting cells.

Sample of code please for our perusal.


Gord Dibben MS Excel MVP

On Tue, 06 Feb 2007 21:37:35 GMT, "brownti" <u31540@uwe wrote:

i just made a bunch of little macros to hide/unhide things and noticed that
when i insert cells above the cells that are written in my macros, it now
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...

"locking" links
 

Here is the code i am using. it repeats a bunch of times for many different
ranges of cells...thanks,

Sub a_brass_finish()
Application.ScreenUpdating = False
Sheets("New Labels").Select
Rows("1976:1996").Select
Selection.EntireRow.Hidden = False
Sheets("BUILDER").Select
End Sub



Gord Dibben wrote:
Without seeing your code it is hard to say but if you hard-coded the cell
references you will get incorrect ranges after inserting cells.

Sample of code please for our perusal.

Gord Dibben MS Excel MVP

i just made a bunch of little macros to hide/unhide things and noticed that
when i insert cells above the cells that are written in my macros, it now
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1

"locking" links
 

Give the range a name first then refer to that range name.

Either manually name the range or do it by macro.

Sub create_name()
'hard-coded
Sheets("New Labels").Rows("$1976:$1996").Name = "mylist"
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = True
End Sub

Or for any selected cells/rows "New Labels" sheet

Sub create_name22()
With Sheets("New Labels")
Selection.Name = "mylist"
End With
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = True
End Sub

Insert a few rows above row 1976 then run this from any sheet.

Sub a_brass_finish()
Application.ScreenUpdating = False
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = False
Sheets("BUILDER").Select
Application.ScreenUpdating = True
End Sub


Gord



On Wed, 07 Feb 2007 12:06:39 GMT, "brownti via OfficeKB.com" <u31540@uwe wrote:

Here is the code i am using. it repeats a bunch of times for many different
ranges of cells...thanks,

Sub a_brass_finish()
Application.ScreenUpdating = False
Sheets("New Labels").Select
Rows("1976:1996").Select
Selection.EntireRow.Hidden = False
Sheets("BUILDER").Select
End Sub



Gord Dibben wrote:
Without seeing your code it is hard to say but if you hard-coded the cell
references you will get incorrect ranges after inserting cells.

Sample of code please for our perusal.

Gord Dibben MS Excel MVP

i just made a bunch of little macros to hide/unhide things and noticed that
when i insert cells above the cells that are written in my macros, it now
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...

"locking" links
 

thank you very much! this forum is very helpful, thanks again!!!



Gord Dibben wrote:
Give the range a name first then refer to that range name.

Either manually name the range or do it by macro.

Sub create_name()
'hard-coded
Sheets("New Labels").Rows("$1976:$1996").Name = "mylist"
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = True
End Sub

Or for any selected cells/rows "New Labels" sheet

Sub create_name22()
With Sheets("New Labels")
Selection.Name = "mylist"
End With
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = True
End Sub

Insert a few rows above row 1976 then run this from any sheet.

Sub a_brass_finish()
Application.ScreenUpdating = False
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = False
Sheets("BUILDER").Select
Application.ScreenUpdating = True
End Sub

Gord

Here is the code i am using. it repeats a bunch of times for many different
ranges of cells...thanks,
[quoted text clipped - 18 lines]
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200702/1

"locking" links
 

how do i give numerous ranges a name without having to manually do all of
them?

i want to do this:

Sub create_name()
'hard-coded
Sheets("New Labels").Rows("$1976:$1996").Name = "brass_1"
Application.Goto Reference:="brass_1"
Selection.EntireRow.Hidden = True
Sheets("New Labels").Rows("$1999:$2009").Name = "brass_2"
Application.Goto Reference:="brass_2"
Selection.EntireRow.Hidden = True
End Sub

what do i need to seperate them by? thanks


Or for any selected cells/rows "New Labels" sheet

Sub create_name22()
With Sheets("New Labels")
Selection.Name = "mylist"
End With
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = True
End Sub

Insert a few rows above row 1976 then run this from any sheet.

Sub a_brass_finish()
Application.ScreenUpdating = False
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = False
Sheets("BUILDER").Select
Application.ScreenUpdating = True
End Sub

Gord

Here is the code i am using. it repeats a bunch of times for many different
ranges of cells...thanks,
[quoted text clipped - 18 lines]
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...

--
Message posted via http://www.officekb.com

"locking" links
 

This is getting a bit beyond my limited VBA capabilities.

You could name and hide each range using an InputBox like so..........

Sub create_names()
Dim i As Long
With Sheets("New Labels")
For i = 1 To 6 'change to your number of ranges
Set mycells = Application.InputBox("Select a Range", , , , , , , Type:=8)
With mycells
.Name = "brass_" & i
.EntireRow.Hidden = True
End With
Next i
End With
End Sub

How you unhide later is up to you.

Maybe something like this..............

Sub unhide()
With Sheets("New Labels")
nameit = InputBox("Type a name", , "brass_")
Application.Goto reference:=nameit
Selection.EntireRow.Hidden = False
End With
End Sub


Gord

On Wed, 07 Feb 2007 21:42:37 GMT, "brownti via OfficeKB.com" <u31540@uwe wrote:

how do i give numerous ranges a name without having to manually do all of
them?

i want to do this:

Sub create_name()
'hard-coded
Sheets("New Labels").Rows("$1976:$1996").Name = "brass_1"
Application.Goto Reference:="brass_1"
Selection.EntireRow.Hidden = True
Sheets("New Labels").Rows("$1999:$2009").Name = "brass_2"
Application.Goto Reference:="brass_2"
Selection.EntireRow.Hidden = True
End Sub

what do i need to seperate them by? thanks


Or for any selected cells/rows "New Labels" sheet

Sub create_name22()
With Sheets("New Labels")
Selection.Name = "mylist"
End With
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = True
End Sub

Insert a few rows above row 1976 then run this from any sheet.

Sub a_brass_finish()
Application.ScreenUpdating = False
Application.Goto Reference:="mylist"
Selection.EntireRow.Hidden = False
Sheets("BUILDER").Select
Application.ScreenUpdating = True
End Sub

Gord

Here is the code i am using. it repeats a bunch of times for many different
ranges of cells...thanks,
[quoted text clipped - 18 lines]
hides/unhides the incorrect things. how can i make it so that my macros will
adjust when i insert cells? i hope it can be done easily...