Help with Code to delete selected ran=ge based on Value of the cell

[rv02]

hi Everyone

I appreciate any help as to what I need to achieve as my VBA knowledge ois not as strong.

I have attached a very simple spread sheet to demonstrate what I need to do.

In the sample sheet what I need is for the code to do the following:
Look at Col G: and if cell =#N/A then select range d:G and delete by shifting cells up. i.e. G4 = #N/A then it selects D4:G4 and delete the contents and shifts cells up. then it looks at the next row and does the same. If G is not #N/A the it will leave it as it is.

In my actual sheet I have over 3000 rows and doing this manually will take days.
It will be nice for the code to start from the a2:g2 and does the above procedure every time G? (?=row reference)=#N/A until G2<>#N/A and then move until G hits another #N/A do the same until g<>#N/A and so on until the last row in the sheet.

Any help is appreciate it.

I hope I make sense.

[TMS]

I am bemused as to why you would want to:

delete the contents and shifts cells up

That will misalign all subsequent rows of data. Is that really what you want? Easy enough but, I suspect, bad idea.


Regards, TMS

[TMS]

Without moving data up ...

Please Login or Register  to view this content.

Regards, TMS

[rv02]

Thankd for this. But I do need to move the data up, will it be possible to have a code for this too

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

[rv02]

Thank you so much for this. I really appreciate your help.

[TMS]

You're welcome. Thanks for the rep.