Macro to move cells to the right based on criteria

Hi Experts,

I know this should be easy but I am struggling with this one. I need to look at column A and for those cells that do not have a value between 1 and 9 I need to move those over to the right one column. If they are between 1 and 9 I need them to stay in place. I am working on a PDF to Excel conversion and the data is not coming in cleanly. I will have a lot of clean up to do so I hate to ask for all of the help at once if I am able to do some of it. I am just stuck on this first piece .

Any help would be greatly appreciated.

I can attach a spreadsheet if needed but I the rest of the data is all over the place at the moment.

Thanks,
theglitch

Thank you very much that worked. I really appreciate the help. I will probably be posting another help soon as I have another one I can't figure out. But thank you very much for your help I will mark closed. Thanks again.

Sorry to bother you again. If you want feel free to not answer. But if I have values in Column B that I want to keep or values that go into other columns say to AG is there a way to shift all of the cells to the right and not overwrite the cell in B with the value in A. Thanks again. I am sorry I am not clear or asking everything at once. I am trying to build this on the fly and I am not clear myself.

Try on a copy of your workbook first.

Please Login or Register  to view this content.

thank you Robert and Jolivanes,

I went ahead and attached a spreadsheet with a before and after of what I am trying to do along with the PDF of what I am starting with. I have the PDF converted to Excel and copied to the cells on the left side of the yellow line. I do not care if the changes happen in place or off to the right like I have them for demonstration purposes. I will try to lay out what I think needs to happen but please ask questions as I am sure I will am not going to be clear.

Thank you again for any help you can provide, I am sorry I did not get back to you last night my internet went out.

In the section to the right of the yellow line. (I do not need the yellow line just there to show the before, on the left and after on the right)

1. move the data over from AH to AI but NOT for AH if the value is between 1 and 9
2. move the data over from AJ to AK but NOT for AJ if AJ has some value in it (I am not sure what that column might have in it yet)(I am guessing similar to what you did already Robert but I would change the criteria)
3. move the data over from AL to AM but NOT for AL if the value is between 1 and 9, in AH
4. move the data over from AN to AO but NOT for AN if AN has some value in it (I am not sure what that column might have in it yet)
5. move the data over from AO to AP but NOT for AO if AO has some value in it (I am not sure what that column might have in it yet)

I think the same process would start over in AP for the second set of data, there are two columns(chunks) of data, see pdf. Please let me know if you need any more data or if I can try to be of any more assistance and thank you both again in advance. I am trying but this is way above my ability.

Thanks,
theglitch

Thank you very much Robert for the response I will try and let you know. I really do appreciate ALL of the help.

Hi Trebor76,

I know I am being a pain and I am sorry, but it is working for columns AH to AO but it it not moving columns AP to AT to arrange them like AH to AO. Is there something I need to do to make both sets of columns work in the code above? Like I said the first set is working perfectly, thank you very much.

Thanks again,
theglitch

Hi Trebor76,

I can get everything but AP through AW to work, I added a new file Book7revised to show what I mean (how AP to AW should look). the two sections in the pdf and the two sections in the spreadsheet should be formatted the same way. The first section works perfectly AH through AO. I am not sure if I need to copy the whole code and try to change for the second section AP though AW or if there is an easier way. I know you are way better and smarter than I am so I really do appreciate all of your help on this.

I really can not say how much I appreciate all of your help,
thglitch

Hi Robert,

I converted All to macros and added to the attached file. The only issue I have now is Column AR. It does not remove the data or clear it out based on any criteria. I am not smart enough to figure out what is the best way to approach this based on what you have done so far. Would you be able to let me know how to do this last piece. Once again that you very much for the help. File attached with what I have done so far.

Thanks,
theglitch

Thank you again for all of your help. I really do appreciate all of your time with this. I think I can take this and make it work. All the best to you.

Thanks again