Defining Ranges

If you protect a worksheet, you cannot remove or add cells. To eliminate any errors that may occur from trying to remove a range from a protected worksheet, you can use the AllowEdit property to determine if you can modify the range. The AllowEdit property returns a Boolean value of True if you can modify the specified range. In the example code, you can use the AllowEdit property to check the range to make sure you can modify a range before you call the Delete method.

Example:

If RangeDelete.AllowEdit Then

RangeDelete.Delete Shift:=xlShiftUp End If

Excel checks the AllowEdit property for the specified Range object. Using the If Then statement ensures that the code only attempts to delete the specified range of cells if you can modify the range. Otherwise, Excel ignores the Delete statement.

You protect worksheets using the Protect method. The type of protection you apply is based upon the parameters you use with the Protect method. See Chapter 9 for more information on using the Protect method to protect a specific worksheet.

Type RangeDelete.Delete Shift:=xlShiftUp, replacing xlShiftUp with the constant indicating how to shift the remaining cell values.

Q Switch to Excel and run the macro.

■ Excel removes the specified range of cells and adjusts the remaining cells accordingly.

Type RangeDelete.Delete Shift:=xlShiftUp, replacing xlShiftUp with the constant indicating how to shift the remaining cell values.

Q Switch to Excel and run the macro.

■ Excel removes the specified range of cells and adjusts the remaining cells accordingly.

HIDE A RANGE OF CELLS

You can hide a specific range of cells using the Hidden property with the Range object that you want to hide. You can use the Hidden property to either change the range of cells that you want to hide or to determine if the range is visible or hidden. You commonly hide portions of a worksheet that contain values that you do not want others to see when they access your worksheet. For example, you may have a worksheet that contains the formulas and the values you use to calculate the displayed data. By hiding the cells that contain the original data, you eliminate a user's ability to view the data you want to keep invisible.

In order to use the Hidden property to hide a range of cells, the range of cells you want to hide must consist of an entire row or column. You make a range hidden by assigning a value of True to the Hidden property for the specified range. Keep in mind that when you hide a range of cells, Excel either sets the width of the columns or the height of the rows to zero making it appear that it is not visible.

You can later verify that the range of cells is still hidden by checking the Hidden property. For example, if you hid column A, you can check to ensure the column is still hidden by typing HiddenRange = Range1.Hidden. If you declare the HiddenRange variable as a Boolean value, the variable receives a value of True if the specified range is hidden; otherwise, it receives a value of False. If you forget to declare the variable as Boolean, Excel assigns a numeric value of -1 if the range is hidden and 0 if the range is visible.

HIDE A RANGE OF CELLS

HIDE A RANGE OF CELLS

□ Create a new subroutine.

< Type Dim EndColumn As Type StartColumn = 1,

Integer, replacing EndColumn replacing 1 with the first with the name of the last column number. column to hide.

□ Create a new subroutine.

Type Dim StartColumn

As Integer, replacing StartColumn with the name of the first column to hide.

< Type Dim EndColumn As Type StartColumn = 1,

Integer, replacing EndColumn replacing 1 with the first with the name of the last column number. column to hide.

0 Type EndColumn = 2, replacing 2 with the last column number.

0 0

Post a comment