Delete Row

[Sharp]

Hi,

does anyone know a code that will search column 'I', and if '#N/A' is found, will delete the entire row and move everything up?

thanks in advance

[Richard Buttrey]

You don't necessarily need VBA code. You could just filter column I for the #N/A and then just select and delete all the filtered rows. If necessary do the same thing with a macro.

HTH

[Sharp]

Hi, thanks,

unfortunatley on this occasion it does need to be VBA as its part of a much larger sheet...

[Richard Buttrey]

Hi,

In that case just get VBA to undertake the filtering and deleting for you. I only said that you don't necessarily need VBA. VBA can do anything you can do manually.

Rgds

[Sharp]

Hi,

i appreciate the input, but the rows need to be deleted...other sheets looks up into it...

[Richard Buttrey]

But that's exactly what I'm suggesting.

First have the macro do the filtering, then have it select the flltered rows, (or filtered cells if you don't want to delete the entire row), and delete them. Or am I missing something?

As I said all you're asking the macro to do is something which you can do manually.

Rgds

[Sharp]

the amount of rows that need to be deleted will change week on week, so i can't record a macro to achieve this

[Richard Buttrey]

Hi,

Who said anything about recording a macro?

Your original post simply mentioned searching column I, which is what I was answering. The easiest way to 'search' a column for defined values is to filter it. Unless you don't want to delete some of the filtered #N/A values, then I fail to understand where the number of rows enters the equation. Either you want to find the #N/A cells or you don't. If you do, surely the 'amount of rows' is irrelevant.

If therefore you're not talking about finding and deleting all the #N/A values my answer will be different.

I didn't say anything about recording a macro. You've just identified one of the big deficiencies in a recorded macro. If there's a specific subset of column I that you want to filter, and somehow the numbers of rows are relevant, then the first thing you need to do is have the macro work out what those rows are. NOTE - you can't do this if you simply record a macro, but you can certainly get VBA to work it out.

Then when you know this relevant range, just use the filtering and deleting I mentioned earlier.

HTH