1

I'm trying to delete the values between the following two Excel ranges:

  • ActiveSheet.Range("A10").Offset(1, 1)
  • ActiveSheet.Cells.Find("Sub Total").Offset(-1, 0)

Do I need to store the above two values in some variable or perhaps there is a better way doing this as the following won't compile:

ActiveSheet.Range("ActiveSheet.Range("A10").Offset(1, 1):ActiveSheet.Cells.Find("Sub Total").Offset(-1, 0)").SelectSelection.ClearContents
Vityata
  • 42,633
  • 8
  • 55
  • 100
sean
  • 11
  • 2

1 Answers1

1

First declare the two ranges rangeA and rangeB and assign them to the one you wanted. Then, getting their top left cell with the .Cells(1, 1) property, assign these to rangeC.

Using ActiveSheet is not advised, but it will work:

Sub TestMe()
    
    Dim rangeA As Range
    Dim rangeB As Range
    
    Set rangeA = ActiveSheet.Range("A10").Offset(1, 1)
    Set rangeB = ActiveSheet.Cells.Find("Sub Total").Offset(-1, 0)
    
    Dim rangeC As Range
    Set rangeC = ActiveSheet.Range(rangeA.Cells(1, 1), rangeB.Cells(1, 1))

    rangeC.Select

End Sub
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • @BigBen - yes, this one will work as well. – Vityata Oct 14 '20 at 17:28
  • @BigBen - it is a philosophic question - I would do it with `.Row` and `.Column`, if the ranges are not of one cell, but of many. This way, I will always get the top left and I will be 100% sure what is going on. – Vityata Oct 14 '20 at 17:30
  • @BigBen - yes, `range.Cells(1, 1)` will work quite nicely. – Vityata Oct 14 '20 at 17:34