Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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... |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
"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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlinks - Move file to local drive, all links break | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions | |||
Prompt to update links | Links and Linking in Excel | |||
Deleting links to other spreadsheets | Excel Worksheet Functions | |||
can't update links...can't find links | Excel Discussion (Misc queries) |