Using Variables to Reference Ranges

[CKRebel]

Thanks to all who help with this simple matter!

This is a fairly straight-forward question: When I need to reference ranges that are constantly changing, how do I construct the range in VBA?

For example: I create a table starting with cell A7. I have a few calculated values in columns B, C, and D (7). I have a macro that when I click a button, it increments cell A7's value by 1 and pastes the value in cell A8. I do this using a + 1 counter variable I call "RowNum". Say I would like a border to appear around the values at the bottom of this range (columns A, B, C, and D). To reference it, I thought I could call it .Range("A7:"A" & RowNum), but this is quite incorrect. What would be the proper way in VBA to reference this sort of range?

[patel45]

To reference it, I thought I could call it .Range("A7:"A" & RowNum), but this is quite incorrect.

why do you think so ?

[CKRebel]

Because individually I can reference a cell as .Range("A" & RowNum).. When putting it in a range such as the one described above, this doesn't work.

[Leith Ross]

Hello CKRebel,

What is the initial value of cell "A7"?

[patel45]

attach please a sample of your file with macro

[CKRebel]

The initial value of cell A7 is 1. What I'm trying to do is create a border around this range, and then when I click a button to add another number "2" in A8, the border appears below A8:D8

[Leith Ross]

Hello CKRebel ,

Please Login or Register  to view this content.

[CKRebel]

With a slight modification, I was able to make this function work in my spreadsheet. Thank you very much for your help Leith.

Marked thread as solved and added to your reputation..