"locking" links

[brownti]

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...

 

[Gord Dibben]

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

 

[brownti via OfficeKB.com]

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]

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

 

[brownti via OfficeKB.com]

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

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]

 

[brownti via OfficeKB.com]

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]

 

[Gord Dibben]

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

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...