Conditional Delete Cells to the Right

[learnerspermit]

Hello, I was wondering if anyone knows how to do this:

If cell A1 does not contains the words "account", "fund", or "number" than delete cells to the right until cell A1 does contain "account", "fund", or "number"

Thanks for any help!

 

[RSXchin]

That would continue forever unless you're deleting a1. Is that what you mean?

 

[learnerspermit]

Yes, exactly ... if A1 does not contain "account", "fund", or "number" than delete it and move all the cells to the right, left one cell, e.g. B1 goes to A1 and C1 goes to B1, etc., etc. until A1 does contain "account", "fund", or "number"

 

[learnerspermit]

Basically, what happened is I imported a .csv file and when cells contained more than a certain number of characters, it split it into multiple cells. But I need the columns to line up correctly, so I would just like to delete the extra cells that were created. I could do this for a few cells manually, but this worksheet is pretty long.

 

[RSXchin]

here you go. it stops if it has those things you said OR if it's blank

Sub Account_or_something()
    Do While [a1] <> "account" And [a1] <> "fund" And [a1] <> "number" And [a1] <> ""
        [a1].Delete shift:=xlToLeft
    Loop
End Sub

 

[learnerspermit]

That worked perfectly. Thank you so much!

 

[RSXchin]

s'kool

 

[learnerspermit]

I apologize for this rudimentary question, but what if I wanted to continue doing this for A1, A2, A3, etc., etc., until all the rows are fixed, i.e. do the same thing as above, but for the entire column? Again, thanks so much!

 

[learnerspermit]

So, if after A1 contains "account", "fund", or "number", go to A2 and delete A2 until A2 contains "account", "fund", or "number", and then go to A3 and delete A3 until A3 contains "account", "fund", or "number"

 

[RSXchin]

Sub Account_Stuffs2()
LC = Cells(1, Columns.Count).End(xlToLeft).Column
    For i = LC To 1 Step -1
        If Cells(1, i) <> "account" And Cells(1, i) <> "fund" And Cells(1, i) <> "number" Then
            Cells(1, i).Delete shift:=xlToLeft
        End If
    Next i
End Sub