Macro to remove all the rows that have no cell value = N/A

[IonutC]

Hi guys,

i did a big report in order to see which clients i have now in the current report and which were not into the past ones.

So I used this vlookup function and i want to remove the rest of the rows that have no value N/A into col.K

Can you please advise how can I do this macro?

Thank you,
Ionut

[mehmetcik]

Please Login or Register  to view this content.

[IonutC]

Thanks man! However I got an error that I have to dim the rngFind. I think this is range no?

[IonutC]

Hi mehmetcik Is not working also with range

Thank you,
Ionut

[mehmetcik]

Have you set this to option explicit?

Please Login or Register  to view this content.

[IonutC]

Hi,

Is still not working.
rng.png
Thank you,
Ionut

[AB33]

you need to define the variable rngFind as a range.

You do not need a code. Just filer the column by N#A and delete the rows. You can also include if error with the lookup error.
If you have a formula with errors and want a code, try this one.

Please Login or Register  to view this content.

[IonutC]

Hi,

I wrote this macro to find the value "#N/A" but i don;t know how to add it into a loop.
The value is only into col.K but i want it to be part of a loop.

Please Login or Register  to view this content.

Thank you!
Ionut

[Winon]

Hello IonutC,

The Code mehmetcik, gave you works flawlessly, even without Option Explicit.

It works for me on a Workbook I have setup for this issue, but if you still have problems with it, it might be that your Workbook contains other Code Criteria, which might be playing silly buggers with us.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachements to open the upload window.

View Pic

Regards.

[Leith Ross]

Hello IonutC,

Here is a shorter macro that will do the job.

Please Login or Register  to view this content.

[Winon]

Hi Leith,

Don't know what I am missing, you Code doesn't do it for me.

This however seems to work:

Please Login or Register  to view this content.

King regards.

[Winon]

Post was duplicated, and removed.

[dmcgov]

Hi,

Is still not working.
Attachment 449559
Thank you,
Ionut

in your dim statement, you have rngFind spelled wrong, you have Dim rngFing

[AB33]

You do not need to loop over the rows, use a simple filter code.

[Leith Ross]

Hello Tjaart,

I ran the code on my laptop with Vista as a double check and it worked with no problems.

The only difference is you removed the code to check if the cell contains an error. Not sure what the problem would be. VarType is a standard VBA function.

[Winon]

Hi Leith,

I have again tested your code with the VarType etc. extensively, and it still does not come to the Party in Excel 2010

[IonutC]

Hi guys,

I want to THANK YOU!

You really helped me a lot and I hope i will become an expert also in time.
I started to learn the code 2 month ago so all your help is really needed!

Have a great week ahead,
ionut

[Winon]

Hello IonutC,

You are welcome, glad we could help.

Thank you for adding to my Reputation!

Regards.