to move the range of data


I have a question on marco in an Excel worksheet attached, Thank you.



Victor. Please edit your question to include the detail currently in your workbook (that makes it easier for both contributors and other users to understand the nature of your query without downloading a file). It would also be helpful if your sample data had header rows.
John_Ru (rep: 1267) Jun 7, '21 at 3:35 am
Add to Discussion


Selected Answer


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


Nice guidance, Variatus
John_Ru (rep: 1267) Jun 8, '21 at 4:07 am
Add to Discussion

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.



This is not an "Answer". It's the part of the question you left out when you first asked. One, you shouldn't ask a question which isn't sufficient to get an answer. Two, once you decide to edit the question, edit the question rather than posting an answer that isn't an answer.
This forum tries to be useful to you and others. Many people will read your "question" and your "answer". What do you want them to think of you after they determine that you don't know the meaning of either term?
Variatus (rep: 4729) Jun 7, '21 at 10:42 pm
Add to Discussion

Answer the Question

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