to move the range of data

Victor,

You will find it hard to get code until you start coding yourself. Nothing spiteful about that. It's just no fun for anyone, including yourself, if you don't know what's going on. So, we'll stick to formulas for now.

Key to your need is the way you work. and while you seem to have made a very good effort at explaining that I didn't fully understand. Also, as you know, people come here to read learn learn. So we must stick to one topic per thread. Therefore I would like to offer you a partial solution. Later, I hope you will play with that and we develop the idea further, either in this thread or a new one. The idea is to work with named ranges.

Your two ranges both start in the last used row in column A. In your sample there are data in rows 59:109 which I took to be "sample only". I removed them in my Sheet1 (2). Therefore the last used row is #51 in column A and #53 in column B. Again, I assumed that you would fill column A eventually but that isn't important for this explanation. The important thing is that I find the last used row in column A and that could be changed to column B or any other column, depending upon how you work.

The last used cell's address =ADDRESS(51, 2, 1,1,"Sheet1 (2)") in column B and =ADDRESS(51, 12, 1,1,"Sheet1 (2)") in column L. From there you could move 49 rows up to L3 or 8 rows up for B44, just one column for your second formula or 7 for your first. And this is the formula to find the last used row where everything starts.

=COUNTA('Sheet1 (2)'!$A:$A)+2

As you see, it counts the number of entries in column A and adds 2 because rows 1 and 2 are blank. Note that you aren't allowed more blank cells without changing the formula. Observe that the range shifts down as you add rows because it counts from the bottom.

Now I used the above to create 2 named ranges. I just called them RangeA and RangeB, which are bad names because they are non-descriptive. When you implement the idea I suggest you give names that have a meaning to you. Here are the formulas by which the named ranges are defined.

"RangeA"  =OFFSET(INDIRECT(ADDRESS(COUNTA('Sheet1 (2)'!$A:$A)+2,2,1,1,"Sheet1 (2)")),0,0,-8,7)
"RangeB  =OFFSET(INDIRECT(ADDRESS(COUNTA('Sheet1 (2)'!$A:$A)+2,12,1,1,"Sheet1 (2)")),0,0,-49)

Remember to change the tab names when you deploy the formulas. Now you can change your formulas in the worksheet as follows.

I3 =COUNTIF(RangeA, A3)
and
M3 =RANK($L3,RangeB)

Now, as you add more entries in column A the definitin of the named ranges changes automatically while the names in the formulas remain the same. Just copy the formulas down without change.

Finally, here are two ways for you to test if the ranges have been set correctly.

1. In the Name Manager, if you click on the formula in the RefersTo field the specified range will be marked with a border on the sheet.
2. Since you want to learn VBA, open the VB Editor and type in the Immediate pane (at the bottom) 

   ? Range("RangeA").Address

   and you will see the address of the specified range although it wasn't specified in VBA.

Hi Mr John_Ru, thanks for your reply. It is much easier to understand the whole picture by looking into the worksheet.

For example, when you click on the formula bar of cell "I3" and cell "M3", you can understand the range of cells covered at the present calculation.

I include an illustration in this same worksheet to show the fresh range of data covered in my next calculation needed, after fresh data is added and old data purged. (It is like some sort of "moving average" in financial chart).

Fresh data is added every three days, hence I need a macro to do the repetitive job.

My apology for asking the other contributors and users to download the worksheet.

I attach now a revised version of the same worksheet.

With best regards. Victor.