Shrink a range by a specific number of cells

0
votes

I want to select the range starting from the active cell down to the next non-blank cell but stopping three cells before the bottom.

If I record a macro with the keyboard sequence Shift-End, Shift-Down, Shift-Up, Shift-Up, Shift-Up the recorded VBA is

Range(Selection, Selection.End(xlDown)).Select
ActiveCell.Range("A1:A32").Select

The problem is that the length of the range is hard-coded.

If I try

Range(Selection, Selection.End(xlDown)).Select
Selection.Resize(numRows - 3, numColumns).Select

VBA throws an "Application-defined or object-defined error" (Run-time error '1004') at the Resize line.

2
excelvba
How did you determine numRows and numColumns? – BigBen
Silly me. I was copying code from a website without checking – prepbgg

2 Answers

2
votes

Try this way, please:

Range(Selection, Selection.End(xlDown).Offset(-3)).Select
1
votes

Try,

Sub test()
    Dim rngDB As Range
    
    Set rngDB = Range(Selection, Selection.End(xlDown))
    
    Set rngDB = rngDB.Resize(rngDB.Rows.Count - 3, rngDB.Columns.Count)
    
    rngDB.Select
End Sub

If cell a1 is a reference cell, it is as follows.

Sub test2()
    Dim rngDB As Range
    
    Set rngDB = Range("a1", Range("a1").End(xlDown))
    
    Set rngDB = rngDB.Resize(rngDB.Rows.Count - 3, rngDB.Columns.Count)
    
    rngDB.Select
End Sub