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.
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.
The current region is defined from the active cell. Blank cells are identified and filled with the value directly above them.
ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
Once the cells are filled the current region is selected
Add a macro button containing the Sub with the commands (1.) and (2.)
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
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.
© 2023 SuperExcelVBA | ABOUT