VBA MACROS - keyboard shortcut range code

[Sulrc]

Hello,

I’m not sure this is the best way to go about it..

But I need code to get a specified range in a macro. The range is variable .. so I’m trying to use keyboard shortcuts like “ctrl + home” and “ctrl + end” to get the correct range.

The problem is when I start let’s say at the bottom right cell of my range and click “ctrl + home” this selects and brings my range all the way to the top. Then I click “shift + cell (down 5rows)” for the correct range.

My problem is .. it works when I manually press these keys on the keyboard. When try this type of code in a macro .. I think it uses the top left most as my active cell and not my most bottom right cell as active cell.

So essentially I have not been able to figure out a right way to make a macro that selects the specific “range” I would like which is variable. But I am able to choose the correct range when I use keyboard shortcuts. I cannot just choose a specific range because the range does change.

Thanks for any feedback and reading through !!! Please comment if you have any further questions.

 

[James006]

Hi,
From any cell in your ... "specified range" , as you say it, use .CurrentRegion

 

[Sulrc]

Hi,
From any cell in your ... "specified range" , as you say it, use .CurrentRegion

Thank you for your reply! I will try this out and try to paste some code for reference. I am quite new to macros!

 

[James006]

Do not hesitate to get back to the Forum with the results of your tests