Copying named range data to a worksheet


Hello, I have a sheet with data that take15 rows per entry. When the number of data columns get to 50 I drop down 16 columns from top row and continue recording the 15 rows of data. I want to keep a rolling 30 day average but obviously when there are less than 30 columns of data in the new rows I need to get the data from two places. I think the easiest way to do this is to copy 29 columns from the previous set, calculate my average and then delete those 29 columns. I think the easiest way to do this is name a range with the data I want to copy, paste it in the appropriate place and then delete it when I am done. My problem is pasting the range. What follows is code I am using:

ActiveWorkbook.Names.Add _
        Name:="Mvrnge", _
        RefersTo:=Sheets(ws_data).Range("c3: g18")
Set Mvrnge = Sheets(ws_data).Range("c3: g18")
Sheets("TestII").Range("B2") = Mvrnge

Nothing happens when I run this but if I manually type =Mvrnge into any cell the range data does what I expect and then I can clear that one cell and all of the data goes away. Mvrange is dimensioned as a range earlier. I tried setting the cell to =Mvrnge and it was changed to =@Mvrnge. What am I doing wrong?




This is the line that makes it work:

Range("Mvrnge").Copy Worksheets("TestII").Range("B2")

but I can't delete the range by clearing one cell


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 

' or
' or

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))


It is a very astute observation that I have the 15X50 format for display purposes. What I have done short term is not worried about the 50 columns and just let the data run to do my averages.
My purpose for tracking this data is as much to help myself learn VBA as it is out of curiousity. I do have a form that I track the averages on and I will try and include an image of it when I get done typing.
I started recording macros and working off the syntax from them to learn. I then started doing online tutorials and jst searching online for what it was I am trying to do and copying that syntax. I am certain that if you saw the code I have written to accomplish what I have done with this workbook so far you would have a dozen different ways to make it shorter. As I learn I am finding those ways and incorporating them but I need to see how what I am trying to do works before I add new methods to it.
My reasoning for using the named range is because if I do the calculation manually I can move all the data with an entry in one cell and delete it all by clearing the one cell. I'm finding what can be done manually is not always that easy in VBA.
Here's what my data screen looks like, it all works.
baddog1016 (rep: 12) Feb 26, '21 at 9:25 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login