Need to search and find text, then delete that cell and the cell to its immediate right. Then move the cells up.
Search would be anywhere on the worksheet. Not sure a sample will help much, but it is attached.
This is what is in the workbook, I want to be able to search for TEST TOTAL, and it delete that and "21" and then move cells up.TEST
A 17
B 1
C 1
D 1
E 1
TEST TOTAL 21
try this:
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Beautiful!
If I have a dozen searches to perform do I just do?
Please Login or Register to view this content.
also, how do you get it to repeat through the sheet? It isn't going through the entire worksheet. And oddly I have one instance of cells shifting to the left and that cell isn't even on the same row as a "Total".
Last edited by taylorsm; 10-24-2017 at 06:27 PM.
You will need a separate If --Then statement for each item you wish to look up.
Here is a link to understand Instr
https://www.techonthenet.com/excel/formulas/instr.php
Thanks. I think I can get the additional IFs. That really only leaves why it isn't looping through the entire sheet and why one cell is getting moved to the left randomly.
I attached a sample. If you run your code you'll see what I mean. It doesn't remove all the Totals and M10 gets moved to the left one cell.
You might use this
Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
Thanks!
Can we make it where if I have "X Total" "Z Total" "NO Total" that the search will apply to all cells with total in it?
is that just the xlWhole to xlPart? I tested it and that looks right, but are there any unintended consequences?
As long as you don't have any cells containing "total" that you want to keep, it will be fine. Same applies to your other search terms of course. Remembering also that it's not just a complete word match, so "total" would match "totally" or "totalitarian" too.
Ah, OK. I think that will work. Is there another option that would only do * Total, Total, or Total *, but not totally?
Yes, you can actually use wildcards if you use xlWhole, so just include the other options in the itemArray
for example.Please Login or Register to view this content.
Ah! Thanks!
What do I need to look at to learn how to do something similar, like searching for text, then copying the cell next to it and then three down? So Like A1:B4?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks