VBA - Move row up if text is found.

[nobodyNZ]

Hi Guys,

I need a VBA coding to find a specific text in cells.
1. If text is found in that cell. Move that row up 1.
2. If text is not found, then do nothing.

Says my worksheet "Test" has about 100 rows (1:100) of data from Column A to H.
Now IF the word "Gone" appear in any cells of Column C, then move the cells from B to H (Same row with text "Gone") up by 1 row. Finally, remove the entire row that has just been moved.

Thanks Guys,

[David A Coop]

Please post a sample workbook, because what you have asked for doesn't seem to make sense.

You would end up with Column A longer than the rest by the number of times "gone" appears in Column C! Is that what you want?

DAC

Please remember, your request is clearer if you attach a sample workbook.
- Click on Go Advanced and click on the Paper Clip.
When you reply please make it clear WHO you are responding to by mentioning their name.
If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.
Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[nobodyNZ]

Hi DAC,

Thanks for your reply.
Sorry I didn't make it very clear. Here is an example:

A 1 2 3 4 5 6 7 8 9 10

B 564 321 51 32 51 21 78 48 87 213

C rw a Gone qwe qwe sasad sd Gone asd as

D sdcx c xzczxv 545 asada xcz 2123 5646 cxz cxz

E 215 68 468 4 2132 32 3 21 530 0.2

Result should be:

A 1 2 4 5 6 7 9 10

B 564 51 32 51 21 48 87 213

C rw Gone qwe qwe sasad Gone asd as

D sdcx xzczxv 545 asada xcz 5646 cxz cxz

E 215 468 4 2132 32 21 530 0.2

*Note that I need to move the whole row that contains the word "Gone" up Except for the number in Column A. After move them up, delete that row. So, the formula here will be something like: move B3:E3 up. Then delete Row 3.
Hope you know what I mean.

Thanks Guys.

[JOHN H. DAVIS]

You're result shows Gone row 3 moved to row 2. However the Gone that starts in row 8 since there are two Gones shouldn't it be in row 6 now on your results?

Please Login or Register  to view this content.

[nobodyNZ]

Hi John,
Thanks for your reply.
I've just tested your coding. It works fine except that it moves the other rows up as well.
As you can see from my example:
The row contains word "Gone" moved up (From B3:E3 to B2:E2). A2 & A3 should remain the same. Then it would delete the entire row that contains number 3 in A3 as well. Reason behind this is that I need the value in Column A remain the same for rows which are not affected.

And Also, if Column C contains: "Something Gone Something", it doesn't move that row up. How can we change it to make it move up as long as the cell contains the word "Gone".
For example, "12nka Gone w1231" still get moved up. But "ksa Gon e 314" wont be moved up.

Thanks in advance !!

[nobodyNZ]

Hi There.

I'm currently using the code below. Which solves the 2nd issue. But it's still not perfect as it moves other rows up as well. =.=

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[nobodyNZ]

Hi John,

It works perfectly !!!
Thanks a lot for your help man.

Cheers !!

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.