98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum 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
Resolved · Urgent Priority · Version 2010
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
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
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...
Thu 4 Sep 2014: Automatically marked as resolved.
Training information:
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Closing Multiple Workbooks quicklyWhen you have several workbooks open in Excel and want to just close them all at once: |