I'm playing around with a class module to try to wrap up some complicated(ish) Range manipulation in Excel-VBA.
I might have a situation where I know the red range spans my target (the target is to have it's contents cleared) and all cells below the red range should go. Green areas are cells used that should not be deleted:
Another example: I only know the red cell - and all used cells just to it's right and below it are to be cleared. Again all green cells should be left alone:
Usually the above is done via manipulation by finding extreme cells (
rows.count...toLeft etc) or the
CurrentRegion property of
Range. I've attempted to wrap that functionality into a Class Module:
Class Module Name:
Private fCurrentRange As Excel.Range '':: '':: Public Enum expandDirection toTheRight = 1 fromTheRight = 2 downwards = 3 fromSheetBase = 4 End Enum '':: read/write property - initial range '':: Public Property Let CurrentRange(ByRef rng As Excel.Range) Set fCurrentRange = rng End Property Public Property Get CurrentRange() As Excel.Range Set CurrentRange = fCurrentRange End Property ':: Expand ':: this will need to use an enumerated type as options ':: Public Sub expand(ByVal aDir As expandDirection) Dim lstCell As Excel.Range Dim lstRow As Long Dim lstCol As Long Set lstCell = Me.CurrentRange.Cells(Me.CurrentRange.Cells.Count) lstRow = Me.CurrentRange.Cells(Me.CurrentRange.Cells.Count).Row lstCol = Me.CurrentRange.Cells(Me.CurrentRange.Cells.Count).Column If (aDir = downwards) Then Dim newLstRow As Long 'newLstRow = lstCell.End(Excel.xlDown).Row newLstRow = Me.CurrentRange.CurrentRegion.Cells(Me.CurrentRange.CurrentRegion.Cells.Count).Row Me.CurrentRange = _ Range( _ Me.CurrentRange.Cells(1), _ Me.CurrentRange.Worksheet.Cells( _ newLstRow, _ lstCol _ ) _ ) End If If (aDir = toTheRight) Then Dim newLstCol As Long newLstCol = lstCell.End(Excel.xlToRight).Column Me.CurrentRange = _ Range( _ Me.CurrentRange.Cells(1), _ Me.CurrentRange.Worksheet.Cells( _ lstRow, _ newLstCol _ ) _ ) End If End Sub
I'm using it like this:
Dim r As TargetRange Set r = New TargetRange r.CurrentRange = wb.Sheets("foo").Range("A2") r.expand downwards r.expand toTheRight r.CurrentRange.ClearContents
- As it stands could the class module be improved?
- Are there some obvious methods missing from the class that could be added?
- Is the whole thing a waste of time? (am I reinventing the wheel?)