Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to zero out non-contiguous ranges
Dean wrote:
<<I am a little confused why you make it a public sub, as well as what you mean by qualifier. Does your approach mean that the macro would be seen by any EXCEL file, and so would save me time if I wanted to do a similar thing in another file? Normally, you start up a macro by using a CommandBar button (in this case named "Clear Named Range" or similar). If the routine were labeled Private, it would not show up in any list box so that you could either assign it to a CommandBar button or start it from the Tools|Macro|Macros dialog box. In general, macros should be written so that they can be used with whatever the currently active workbook is. Dean wrote: <<I will only use it on one worksheet (call it "output", which I will first pre-select within the macro) and only within this one workbook. I just want to put it in an existing garden variety module, I think. Can you rewrite your set command with this more simply-specified range? Assuming that your named range is global to the entire workbook (they usually are, unless you declared it like "output!MyNamedRange"), then the code below can be put in a standard code module, and will work regardless of what worksheet is active at the time the macro is called (the beauty of named ranges!). Notice the change of ActiveWorkbook to ThisWorkbook. This part "qualifies" the next part of the statement, which is the ..Names("MyNamedRange") part. If you had 2 workbooks of this same type open, the macro would not know which workbook you wanted to clear the cells on. This is why it is good programming practice to always clearly specify exactly where you want the macro to operate. ActiveWorkbook means the workbook that is currently active (the one you see on top of all other open workbooks). ThisWorkbook means the workbook that the macro is located in (which might be a workbook in the background). Public Sub ClearMyNamedRange() Dim rngCellsToClear As Range Set rngCellsToClear = ThisWorkbook _ .Names("MyNamedRange") _ .RefersToRange rngCellsToClear.Formula = 0 End Sub Dean wrote: <<Lastly, did you forget the part that would skip over any cells in the, changed-to-one-contiguous block that have protection in their formats, whether or not the sheet is protected? The macro above only changes any cells that are in the named range, so it will automatically skip any cells that are not specifically named in the Defined Name. There should be no need to worry about unprotecting and then reprotecting the worksheet, unless you have some of the cells in the named range protected. Normally, you don't protect cells where the user is supposed to make changes. You only protect cells that contain formulas, because you don't want the logic of the worksheet to be changed. Hope these explanations help. -- Regards, Bill Renaud |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to zero out non-contiguous ranges
Thank you Bill, and Tom. Though I can see the value for some operations,
right now, I am not all that comfortable sharing macros across workbooks. I am confident that I will make sure I am in the correct workbook when I use one. From the posts of the two of you, I have taken indecent liberty and created the following macro. It is producing a variable not defined error, with the prematurely terminated macro highlighting the word "cell" in the command "For Each cell" line. Obviously, I am leaving out, or doing, something stupid. I think the part I refer to as "second part" below is OK. Hopefully, the only error is this one thing. Help, please! Dean Sub Hide_AandD_Rows() Sheets("Output").Select 'first part is to set the percents to zero Range("AandDLoanDrawPercents").Select Dim rngCellsToClear As Range 'from BR on 09-25-07 Set rngCellsToClear = ThisWorkbook.Names("AandDLoanDrawPercents").Refers ToRange For Each cell In Range(rngCellsToClear) 'this part from TO on 09-25-07 If cell.Locked = False Then cell.ClearContents End If Next 'second part is to, then, hide the rows that would show in the main section Range("AandDRows").Select Selection.EntireRow.Hidden = True Range("A9").Select ActiveWindow.ScrollWorkbookTabs Position:=xlFirst Sheets("Beginning Balances").Select End Sub "Bill Renaud" wrote in message . .. Dean wrote: <<I am a little confused why you make it a public sub, as well as what you mean by qualifier. Does your approach mean that the macro would be seen by any EXCEL file, and so would save me time if I wanted to do a similar thing in another file? Normally, you start up a macro by using a CommandBar button (in this case named "Clear Named Range" or similar). If the routine were labeled Private, it would not show up in any list box so that you could either assign it to a CommandBar button or start it from the Tools|Macro|Macros dialog box. In general, macros should be written so that they can be used with whatever the currently active workbook is. Dean wrote: <<I will only use it on one worksheet (call it "output", which I will first pre-select within the macro) and only within this one workbook. I just want to put it in an existing garden variety module, I think. Can you rewrite your set command with this more simply-specified range? Assuming that your named range is global to the entire workbook (they usually are, unless you declared it like "output!MyNamedRange"), then the code below can be put in a standard code module, and will work regardless of what worksheet is active at the time the macro is called (the beauty of named ranges!). Notice the change of ActiveWorkbook to ThisWorkbook. This part "qualifies" the next part of the statement, which is the .Names("MyNamedRange") part. If you had 2 workbooks of this same type open, the macro would not know which workbook you wanted to clear the cells on. This is why it is good programming practice to always clearly specify exactly where you want the macro to operate. ActiveWorkbook means the workbook that is currently active (the one you see on top of all other open workbooks). ThisWorkbook means the workbook that the macro is located in (which might be a workbook in the background). Public Sub ClearMyNamedRange() Dim rngCellsToClear As Range Set rngCellsToClear = ThisWorkbook _ .Names("MyNamedRange") _ .RefersToRange rngCellsToClear.Formula = 0 End Sub Dean wrote: <<Lastly, did you forget the part that would skip over any cells in the, changed-to-one-contiguous block that have protection in their formats, whether or not the sheet is protected? The macro above only changes any cells that are in the named range, so it will automatically skip any cells that are not specifically named in the Defined Name. There should be no need to worry about unprotecting and then reprotecting the worksheet, unless you have some of the cells in the named range protected. Normally, you don't protect cells where the user is supposed to make changes. You only protect cells that contain formulas, because you don't want the logic of the worksheet to be changed. Hope these explanations help. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to zero out non-contiguous ranges
Hi Dean,
Yes, the problems with mixing code from 2 different authors! Tom did not declare his variables, and I always use "Option Explicit" at the top of my code modules, so that is what is producing the "variable not defined error". The variable "cell" was from his code, which was not defined in my routine. Try this version for the Clear routine. It sets the value of the unlocked cells to zero, rather than clearing each cell. Using ClearContents clears the cell of all formulas and values, so it will be blank just like a new worksheet. Public Sub ClearAandDLoanDrawPercents() Dim rngCellsToClear As Range Dim rngCell As Range Set rngCellsToClear = ThisWorkbook _ .Names("AandDLoanDrawPercents") _ .RefersToRange For Each rngCell In rngCellsToClear With rngCell If Not .Locked Then .Value = 0 End With Next rngCell End Sub I think "Hide_AandD_Rows" should be a separate routine. I don't quite understand that part yet. -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to zero out non-contiguous ranges
Yes, I took Tom's out of context. The point of the subroutine is to hide
some rows but, first, to make sure all their inputs are zero. That's why I have them together. I would never do the 2nd without the first. I know you experts think this is bad technique! Sorry. One other thing, while I'm here. This is a question about EXCEL, not macros. Seemingly all of a sudden (though probably not), I am noticing that most of my inputs that are zero are showing as blank which, often, I'd rather not be the case - I'd like to see the zero, or 0%. I checked the usual suspects, such as conditional formatting, white fonts, protection-hidden but none were the cause. It seems that this is simply the default of some of EXCEL's common formats, such as integer and percent. Somehow, I don't remember such being the case, but I am probably just losing it! Though I know I can use custom formats, I don't like to deal with them any more than necessary and so I ask: If what I suggested above is true, is there a way to change EXCEL's simple formats, the kind that have those clickable buttons? Macro works nice now. Thanks! Dean "Bill Renaud" wrote in message . .. Hi Dean, Yes, the problems with mixing code from 2 different authors! Tom did not declare his variables, and I always use "Option Explicit" at the top of my code modules, so that is what is producing the "variable not defined error". The variable "cell" was from his code, which was not defined in my routine. Try this version for the Clear routine. It sets the value of the unlocked cells to zero, rather than clearing each cell. Using ClearContents clears the cell of all formulas and values, so it will be blank just like a new worksheet. Public Sub ClearAandDLoanDrawPercents() Dim rngCellsToClear As Range Dim rngCell As Range Set rngCellsToClear = ThisWorkbook _ .Names("AandDLoanDrawPercents") _ .RefersToRange For Each rngCell In rngCellsToClear With rngCell If Not .Locked Then .Value = 0 End With Next rngCell End Sub I think "Hide_AandD_Rows" should be a separate routine. I don't quite understand that part yet. -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to zero out non-contiguous ranges
Dean wrote:
<<...is there a way to change EXCEL's simple formats, the kind that have those clickable buttons? I suppose you could override the buttons by assigning them to a new macro that you would write, but I think it is simpler to just use Styles. Using the Customize dialog box, add the Styles combo box to your formatting toolbar (see Excel Help for your version to do this). I have a template that has all of the custom styles that I need for any worksheet. When building a new workbook, I simply select a bunch of cells that will all be the same format, then choose a style from the Styles combo box. A Style can apply a number format, borders, cell protection, etc. all in one selection from the combo box. -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to zero out non-contiguous ranges | Excel Programming | |||
Non contiguous ranges | Excel Programming | |||
vlookup on non-contiguous ranges | Excel Worksheet Functions | |||
Adding Non-Contiguous Ranges | Excel Discussion (Misc queries) | |||
sorting non contiguous ranges | Excel Discussion (Misc queries) |