| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
referencing named ranges in VBA
I have the following macro, which hides and unhides some rows:
Sub ShowMWDC() ' Freezes window at cell A41 ' Unhides rows 41:83 ' Hides rows 2:39 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("41:83").Select Selection.EntireRow.Hidden = False Rows("2:40").Select Selection.EntireRow.Hidden = True Range("A44").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Rows 41:83 correspond to a named range, "MWDC" and rows 2:40 correspond to a named range, "Migration". How would I modify the above to use these named ranges as opposed to the rows? Thanks, Dave -- Brevity is the soul of wit. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing named ranges in VBA
Hi, Dave-
Try this one: Range(Names("MWDC").RefersTo).Select Dave O |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing named ranges in VBA
Just replace
Rows("41:83").Select with Range("MWDC").Select etc. -- Gary's Student gsnu200702 "Dave F" wrote: I have the following macro, which hides and unhides some rows: Sub ShowMWDC() ' Freezes window at cell A41 ' Unhides rows 41:83 ' Hides rows 2:39 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("41:83").Select Selection.EntireRow.Hidden = False Rows("2:40").Select Selection.EntireRow.Hidden = True Range("A44").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Rows 41:83 correspond to a named range, "MWDC" and rows 2:40 correspond to a named range, "Migration". How would I modify the above to use these named ranges as opposed to the rows? Thanks, Dave -- Brevity is the soul of wit. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing named ranges in VBA
Gary's kung fu is better: I'm going to change my code archive.
|
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing named ranges in VBA
Well that's easy. Probably should have tried that one on my own.
Thanks. -- Brevity is the soul of wit. "Gary''s Student" wrote: Just replace Rows("41:83").Select with Range("MWDC").Select etc. -- Gary's Student gsnu200702 "Dave F" wrote: I have the following macro, which hides and unhides some rows: Sub ShowMWDC() ' Freezes window at cell A41 ' Unhides rows 41:83 ' Hides rows 2:39 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("41:83").Select Selection.EntireRow.Hidden = False Rows("2:40").Select Selection.EntireRow.Hidden = True Range("A44").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Rows 41:83 correspond to a named range, "MWDC" and rows 2:40 correspond to a named range, "Migration". How would I modify the above to use these named ranges as opposed to the rows? Thanks, Dave -- Brevity is the soul of wit. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
referencing named ranges in VBA
The key point is that the name of a named range is just a string, not a
variable. A very common mistake is to use Range(MWDC) instead of Range("MWDC") -- Gary's Student gsnu200702 "Dave F" wrote: Well that's easy. Probably should have tried that one on my own. Thanks. -- Brevity is the soul of wit. "Gary''s Student" wrote: Just replace Rows("41:83").Select with Range("MWDC").Select etc. -- Gary's Student gsnu200702 "Dave F" wrote: I have the following macro, which hides and unhides some rows: Sub ShowMWDC() ' Freezes window at cell A41 ' Unhides rows 41:83 ' Hides rows 2:39 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("41:83").Select Selection.EntireRow.Hidden = False Rows("2:40").Select Selection.EntireRow.Hidden = True Range("A44").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Rows 41:83 correspond to a named range, "MWDC" and rows 2:40 correspond to a named range, "Migration". How would I modify the above to use these named ranges as opposed to the rows? Thanks, Dave -- Brevity is the soul of wit. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Query and expanding named ranges | Excel Discussion (Misc queries) | |||
| Importing Excel named ranges using MS Query | Excel Discussion (Misc queries) | |||
| INDIRECT and Named Ranges referencing closed workbook | Excel Worksheet Functions | |||
| Excel will not include all of my non-adj ranges in a named range?? | Excel Worksheet Functions | |||
| Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel | |||
Linear Mode
