selecting active cell value

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Selecting from active cell to value in last row | Excel forum

Selecting from active cell to value in last row | Excel forum

resolvedResolved · Urgent Priority · Version 2010

Edited on Fri 22 Aug 2014, 11:35

Sonia has attended:
Excel Advanced course

Selecting from active cell to value in last row

Hi,

I am struggling to code VBA to select from the active cell to the very last cell in the column containing a value.

I would like to skip any blank cells within the column.

Please can you advise.

Regards,

Sonia

RE: Selecting from active cell to value in last row

Hi Sonia

Thanks for getting in touch. There are a number of ways to achieve this. Lots of people like to 'jump' up from the last cell on the spreadsheet, e.g.

Range("A100000").End(xlUp).Select

Will go up from cell A100000 (you can adjust that as required.

And if you record yourself doing CTRL SHIFT DOWN you get this:

Range(Selection, Selection.End(xlDown)).Select

If you then combine these concepts you can get something like this:

Range(ActiveCell, Range("A100000").End(xlUp)).Select

Try this out and let me know how you get on.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Selecting from active cell to value in last row

Hi Gary,

Many thanks for your response.

I had a look at both options you supplied previously, however, I am looking for a code which can work with the following;

1) active cell only (I would like not to specify a range/cell name/address)

2) locates the last value in a column, I am using an extract from a data report which has blank cells therefore using " Range(Selection, Selection.End(xlDown)).Select " will stop at a blank cell, I would like to override this.

Is this possible?

Regards,

Sonia



RE: Selecting from active cell to value in last row

Hi Sonia

Thanks for your reply. There are ways to get around what you want but it would help if I understood a little about what you're trying to do.

Once you've selected the data, what are you doing with it (moving, sorting, deleting etc) ?

There may be an approach that suits the process better.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Edited on Tue 26 Aug 2014, 10:15

RE: Selecting from active cell to value in last row

Hi Gary,

Mainly I would be looking to copy the data and paste it into another workbook.

Regards,

Sonia

RE: Selecting from active cell to value in last row

Hi,,

Please can I have a response to my query.

Many thanks,

Sonia

RE: Selecting from active cell to value in last row

Hi Sonia

The code I provided above will deal with the second issue (from the activecell select all the way down to the last item of data, as it leapfrogs all the blanks.

I'm looking into how to not specify an address but it's not straightforward. Are your ranges different every single time you run this code? Otherwise you could set it up to work across all the ranges specifying the range (e.g. A100000) each time.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Selecting from active cell to value in last row

Hi Gary,

Many thanks for your response.

Please can you let me know where I can send an example of my file for you to have a look, hopefully it will make more sense, as I have tried the Range(ActiveCell, Range("A100000").End(xlUp)).Select option, but it does not work correctly. It seems to want to revert to copying from "A2" which was initial activecell used.

In regards to the data, I can specify the paste destination but in regards to the core data, it may change in the future, therefore I have created a find option to find the column heading and copy from there onwards.

Regards,

Sonia

RE: Selecting from active cell to value in last row

Hi Sonia

One more question. Do you wish to retain the blanks? Because Go To Special (CTRL G) can select everything except the blanks if that helps.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Selecting from active cell to value in last row

Hi Gary,

Yes I will need to retain the structure of the core data.

Regards,

Sonia

RE: Selecting from active cell to value in last row

Hi

Apologies I missed your first bit asking to send the file: please send to forum@stl-training.co.uk FAO Gary Fenn and I'll take a look.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Edited on Thu 28 Aug 2014, 15:56

RE: Selecting from active cell to value in last row

Hi Sonia

I'm just having a look at your code and I can see the issues you're having.

Here's a couple of suggestions:

1. Why not copy *everything* then delete the columns you don't need? You can do this straight after your Find command with an Activecell.EntireColumn.Delete

2. Why not copy the whole column across?

Immediately after Find, you could do:

ActiveCell.EntireColumn.Copy Sheets("Copy data to this worksheet").Columns(2)

I've attached your workbook back and commented out lots of it to focus in on the 'Cusips' column. Have a look and see what you think.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

GF Sonia STL Example.xlsm

Thu 4 Sep 2014: Automatically marked as resolved.

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
application development excel and other training courses.

Excel tip:

Closing Multiple Workbooks quickly

When you have several workbooks open in Excel and want to just close them all at once:

1) Hold down the SHIFT key before selecting the File menu.

2) Once in File menu release SHIFT key and select Close All option.

3) All your files will close. If files require saving Excel will ask if you want to save the changes.

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.