Hello everyone
Im currently working on a spreadsheet at work and im trying to automate it a bit using VBA.
The problem i have is that i am trying to find the last cell with data in column A, column a is full of formulas and when i use the End(xlDown) code it just keeps ending up with the last cell with a formula in but i want it to be the last cell with actual data in
Please help
Last edited by steve_m_2003; 11-29-2011 at 06:15 AM.
Hi,
Not quite sure what you mean by 'data' but
will return the vlalue of the last cell in column A to the variable vCellLast
Please Login or Register to view this content.
Regards
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Are you trying to say you would like to find the last cell that has values and not formulae?
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Hi agasin thanks for the replys sorry i havnt i have been off work. Thaqnks richard but thats not really what i was after
Arlu yes i am needing excel to goto the last cell with a value in instead of going to the last cell with a formula in
I dont think i explained myself as good as i could have sorry
Hope someone can help me
Thanks in advance
How about this?
Please Login or Register to view this content.
Originally Posted by JapanDave
Thanks but i cant get it to work it does nothing
This will ignore formulae and select the last row that has data
Please Login or Register to view this content.
Last edited by Steffen Thomsen; 11-28-2011 at 07:55 AM.
Thanks i have copied it into vba and it has a problemThis will ignore formulae and select the last row that has data
Please Login or Register to view this content.
Expected: list separtor or )
So i put a bracket around A like so
But then it gives me another error of Invalid Qualifier and highlightsPlease Login or Register to view this content.
Please Login or Register to view this content.
hi,
as an option
see if it helps you
Please Login or Register to view this content.
Regards, John55
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
...enjoy -funny parrots-
Sorry, missing a character, updated a bit
Please Login or Register to view this content.
Last edited by Steffen Thomsen; 11-28-2011 at 08:23 AM.
Thanks
For some reason it keeps selecting cell A502
My spreadsheet is 504 rows long and about 50 columns wide and everycell has a forumla to pull the information from all different sheets however it varies day to day how much information goes into it but it never goes past row 504 sometimes there maybe 50 rows worth of data or could be 500 rows of data. This data then needs to be copied into a master spreadsheet to which i do manually but i was trying to make it automatic with vba. The first row of data that gets copied is row 5 and then highlight all the way down to the ast row with data in, like i said it could be row 50 or row 500 but as every row has a formula in i think vba looks at that instead of a value.
Hope this explains it a bit better and i am greatly appericated for all your kind help
Steve
Everycell in
Ok, then try this:
Please Login or Register to view this content.
Last edited by Steffen Thomsen; 11-28-2011 at 09:36 AM.
It goes to row 503 which i dont understand as the last row is 504 but in my test spreadsheet the last row with a value that the formula returned is 446 but it skips past that and goes to row 503
Sorry about this
If you need to copy the range then use this:
This copies the range to another sheet
*EDITPlease Login or Register to view this content.
use this to see if it selects the correct range
Please Login or Register to view this content.
I don't know if it is what you are after, but anyway.
Please Login or Register to view this content.
Nope none of these are working i dont have a clue what is going on but ill stick to my good old trustworthy copy and paste manually
Thanks for all the hhelp tho people
Haven't really followed any of the other suggestions, but try this.
In column A the formula goes down to A17, but the last value is in A5.
HTH
Regards, Jeff
How about you post a dummy workbook with sensitive info taken out, so we can actually see what you want.
Thanks for your input this has half worked, it has given me thwe cell to which i need selecting but i have tried to change the code to select the cell instead of given me a msg box.
I have attached a file to which may help you help me
In this sample the formulas on sheet 1 are looking at sheet 2 but the original looks at many different sheets. But on this sheet 1 i am trying to get vba to select the last cell with a value which is A457 (although its not always A457)
Again thanks for all your help
Here you go.
it will select the last row with data and not a formula.
Please mark the thread as SOLVED if it is
Please Login or Register to view this content.
Here you go.
it will select the last row with data and not a formula.
Please mark the thread as SOLVED if it is
Please Login or Register to view this content.
This selects cell A1 which contains the column labels which have been hand written and do not include a formula, sorry forgot to put column labels on the sample here is a new one
This single line will select the last row with a value in it
Please Login or Register to view this content.
On your sample, this selects cell A458
Please Login or Register to view this content.
Please Login or Register to view this content.
Thats great thankyou everyone for your support
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks