Selected Answer
Hi baddog,
No. You can't delete a range by deleting a single cell unless that single cell is the source of data for the entire range. (That's why Excel adds the @.) But there seeems to be some misconcepotion somewhere. Let me try.
Define a range. Say B5:D15. That's 10 rows x 3 columns = 30 cells. Excel gives this range a name, "B5:D5". By this name you can address it in Excel, like =SUM(B5:D15), or using VBA like Range("B5:D15").Cells.Count. Observe that Excel doesn't need the range name to be in inverted commas but VBA does.
Since "B5:D15" is a name the range is a "named range". When Excel tells you to name the range, in fact, you are just changing the default name to a custom name. Call it "MyRange". Now you can use the new name in Excel, like =SUM(MyRange) or in VBA, like Range("MyRange").Cells.Count. The default name and the new name are fully interchangeable. Note that the actual range doesn't change with the name by which it is called.
Cell A1 is a single cell. Of course. But what happens if you assign 30 cells to that one cell? In Excel you enter =Range(B5:D15) in cell A1. In VBA you say Cells(1, 1).Value = Range("MyRange").Value. Logically, both efforts should result in an error because, obviously, it isn't possible to enter 30 cells into one. But actually, Excel may process the spill (as it's called). The 29 extra cells are displayed to the right and below A1.
It stands to reason that whatever spill from A1 might be displayed in A1:C10 should be an extension of the range projected into A1 from B5:D15. We come to a difference between a cell's value and display, between Formula and Value and, ultimately, also between Value and Value2 (the formula's result and the way that result is displayed). But VBA takes a much less sophisticated view. For VBA, a cell must have a value and that value can be deleted.
Therefore, finally coming to your question, it doesn't matter whether the range is named "B5:D15" or "MyRange". If you command Range("whatever").ClearContents the cells should become blank. Note the difference between Delete and ClearContents. Either way, the number of cells in your worksheet will not change. With the Delete command other cells in the sheet will be moved to fill the void.
There is a difference between a named range, which is a feature of Excel, and a range variable, which is created by VBA. You can assign a range, under whatever name you have given to it in Excel, to a VBA variable.
Dim MyRange As Range
Set MyRange = Range("MyRange")
Now the VBA variable MyRange refers to the same cells as the Excel named range "MyRange" which, ultimately, is B5:D15. Therefore
MyRange.ClearContents
' or
Range("MyRange").ClearContents
' or
Range("B5:D15").ClearContents
all have the same effect. Of course, you could also use VBA to say this:-
Cells(1, 1).Value = Worksheetfunction.Average(MyRange)
In this line MyRange is a VBA variable (no inverted commas). But within the function it could be replaced with an Excel named range (by any name). Please ponder the implications for a moment. You wouldn't need to name a range in Excel in order to define a range in VBA. You could define the range in your 15 x 50 list consisting of two half rows and never create a display of that range in a single row on your worksheet. In fact, the AVG function supports drawing the average from several ranges. Therefore the following should also work
Range("MyCells(1, 1).Value = Worksheetfunction.Average(FirstHalf, SecondHalf)
Here, FirstHalf and SecondHalf are range variables created in VBA. They have no equivalent in Excel and where created by syntax for setting a range:-
Set FirstHalf = Range([First cell], [Last cell])
' or
Set FirstHalf = Range(Range("B5"), Range("D15"))
' or
Set FirstHalf = Range(Cells(5, "B"), Cells(15, "D"))
' or
Set FirstHalf = Range(Cells(5, 2), Cells(15, 4))
