Current Region

Basic Exercise

Statement

Add a button to a worksheet and assign it to a macro the selects the current region. If any data are missing complete them with the value directly above.

Suggested Concepts:

• If Else - Conditionally executes a group of statements, depending on the value of an expression.
• CurrentRegion - Returns a range that represents a region. Next to this property, we recommend using the SpecialCells method with the xlCellTypeBlanks parameter.
• FormulaR1C1 - Uses a R1C1 annotation (Row, Colunm) to fill cells in a range region.
• ActiveCell - It references the active cell in the worksheet.
• Select e Selection - Selects and references the selected range.

Note: the function If Else was applied to the solution of the exercise only to avoid an error to occur. This happens when the macro treats the exercise statement as part of the CurrentRegion. Note that to overcome this error the Intersect method was applied as shown in the previous exercise Intersection.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Use FormulaR1C1 reference style to reference blank cells.

    Sub Solution()

    If Intersect(ActiveCell, Range("A1:AC1,C2:C24,F2:F24,I2:I24,A25:AX38,J2:AX24")) Is Nothing Then
    'This if condition only ensures that the selected area is of interest. Avoiding errors with problem statement

        ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        ActiveCell.CurrentRegion.Select
    End If

    End Sub

• Previous exercise
• Next exercise

• report error/feedback

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

© 2024 SuperExcelVBA | ABOUT