| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
why does this macro select the entire worksheet when run?
Here's the macro:
Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why does this macro select the entire worksheet when run?
Dave, Cells.Select
-- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave F" wrote in message ... Here's the macro: Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why does this macro select the entire worksheet when run?
Well that would be the obvious answer. However, when I run a different
macro, the entire worksheet is not selected: Sub ProjectSummary() 'Hides rows 4:94 'Freeze window at E97 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("4:94").Select Selection.EntireRow.Hidden = True Range("E73").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub So I'm not sure Cells.Select is the issue. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Paul B" wrote: Dave, Cells.Select -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave F" wrote in message ... Here's the macro: Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why does this macro select the entire worksheet when run?
If you activate a cell in the current selection, it doesn't change the
selection. It just changes the activecell. Range("B1").select would change the selection to just B1. Dave F wrote: Here's the macro: Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why does this macro select the entire worksheet when run?
Ah, thanks.
Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Dave Peterson" wrote: If you activate a cell in the current selection, it doesn't change the selection. It just changes the activecell. Range("B1").select would change the selection to just B1. Dave F wrote: Here's the macro: Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why does this macro select the entire worksheet when run?
Dave,
I assume that you mean why is the whole sheet remaining selected because if you run: Sub ProjectSummary() 'Hides rows 4:94 'Freeze window at E97 Cells.Select MsgBox "Dave" Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("4:94").Select Selection.EntireRow.Hidden = True Range("E73").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub You will see that the whole sheet is being selected. The differencebetween the macros is that in this one you later make another selection and that is Rows("4:94").Select But why Select them in the first place? Try it with the Cells.Select line removed. As others have said in the NG's selecting slows up macros and it is seldom necessary to select object before carying out operations on them. Sub ProjectSummary() 'Hides rows 4:94 'Freeze windowCells.Select With Range("B1") .EntireRow.Hidden = False .EntireColumn.Hidden = False End With With Rows("4:94") .EntireRow.Hidden = True End With Range("E73").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub More programming but they tell me that it runs faster Incidentally why activate a cell in the hidden range? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Dave F" wrote in message ... Well that would be the obvious answer. However, when I run a different macro, the entire worksheet is not selected: Sub ProjectSummary() 'Hides rows 4:94 'Freeze window at E97 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("4:94").Select Selection.EntireRow.Hidden = True Range("E73").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub So I'm not sure Cells.Select is the issue. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Paul B" wrote: Dave, Cells.Select -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave F" wrote in message ... Here's the macro: Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
why does this macro select the entire worksheet when run?
Thanks for that explanation. I've removed Cells.Select
The macro was based on another one I copied from, and so I left the Cells.Select in. But it seems to do what I need it to do without Cells.Select. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Sandy Mann" wrote: Dave, I assume that you mean why is the whole sheet remaining selected because if you run: Sub ProjectSummary() 'Hides rows 4:94 'Freeze window at E97 Cells.Select MsgBox "Dave" Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("4:94").Select Selection.EntireRow.Hidden = True Range("E73").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub You will see that the whole sheet is being selected. The differencebetween the macros is that in this one you later make another selection and that is Rows("4:94").Select But why Select them in the first place? Try it with the Cells.Select line removed. As others have said in the NG's selecting slows up macros and it is seldom necessary to select object before carying out operations on them. Sub ProjectSummary() 'Hides rows 4:94 'Freeze windowCells.Select With Range("B1") .EntireRow.Hidden = False .EntireColumn.Hidden = False End With With Rows("4:94") .EntireRow.Hidden = True End With Range("E73").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub More programming but they tell me that it runs faster Incidentally why activate a cell in the hidden range? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Dave F" wrote in message ... Well that would be the obvious answer. However, when I run a different macro, the entire worksheet is not selected: Sub ProjectSummary() 'Hides rows 4:94 'Freeze window at E97 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Rows("4:94").Select Selection.EntireRow.Hidden = True Range("E73").Select ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub So I'm not sure Cells.Select is the issue. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Paul B" wrote: Dave, Cells.Select -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Dave F" wrote in message ... Here's the macro: Sub UnhideAll() 'Unhides all rows 'Freezes window at E4 Cells.Select Range("B1").Activate Selection.EntireRow.Hidden = False Selection.EntireColumn.Hidden = False Range("E4").Activate ActiveWindow.FreezePanes = False ActiveWindow.FreezePanes = True End Sub Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need a macro select all cells in a worksheet? | Excel Discussion (Misc queries) | |||
| Want macro to select & copy cells from a different worksheet | Excel Discussion (Misc queries) | |||
| Macro that double spaces rows for an entire worksheet? | Excel Discussion (Misc queries) | |||
| Macro to print a selected range, not entire worksheet | Excel Discussion (Misc queries) | |||
| select worksheet to run macro | Excel Discussion (Misc queries) | |||
Linear Mode
