Remove last 2 characters of column A
Hello,
I want to make a macro that removes the last 2 characters of every cell in column A. The amount of rows in column A will vary with each workbook. So it needs to select the entire column A.
It's for the first sheet in the workbook.
I'am not experienced with VBA at all, so all the help will be greatly appreciated.
-Gunther
This should work, assuming all cells have more than 2 characters in column A and assuming you have a column header in A1.
Please Login or Register to view this content.
Last edited by mikeTRON; 08-02-2016 at 05:39 PM.
Please ensure you mark your thread as Solved once it is. Click here to see how.
If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
Just tried it and it works perfect, thanks a lot for taking the time and the fast response!Originally Posted by mikeTRON
This should work, assuming all cells have more than 2 characters in column A and assuming you have a column header in A1.
Please Login or Register to view this content.
Since I've finished before realizing that there is a replay already and my macro is slightly different I'll let myself post it
Please Login or Register to view this content.
Looks good, i' ll try them both. Thanks!
Bartos works assuming you want to start with row 1, where I assumed you had a header in row 1 but he does bring up a good point about making sure the cell is not less than 2 characters. I added a simple IF statement to just skip the cell if the cell has less than two characters.
Please Login or Register to view this content.
Last edited by mikeTRON; 08-02-2016 at 05:43 PM.
mikeTRON
Absolutely true. Gunther09 didn't specify how does his dataset looks like so we work with what we have.
But more importantly, if Gunther09 has lots of cells ( > 10'000 ) then it will take LOTS of time. For one off, it might be OK.
If however he has > 100'000 cells or wants to do it many times than I'd load it to an array and work from there. It will be many many times faster.
Sorry for the lack of information.
Yes i' am using a header so the first macro works good. The data is usually 8 characters.
The column contains at max 500 to 600 cells.
Try this
Please Login or Register to view this content.
Yep, I was mentioning the header row to Gunther more than you.
How would you do it in an array?
This works also.Try this
Please Login or Register to view this content.
Not sure which one to use now
jindon
Very nice one-liner
mikeTRON
code as follows,
it process 1 mln pos in 3.1 sec on notebook with intel i7. My guess is that reading cell by cell would take couple hours.
Please Login or Register to view this content.
Last edited by Bartos; 08-03-2016 at 04:06 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks