Issue with Macro Code to Search via InputBox and Hide

**Reimertoad** · 05-28-2017, 12:47 AM

Greetings,

I'm looking to create a macro that will search column F for a specific value (e.g. 1700-0100), and then hide every row where it locates that particular value in that column. The following code is what I have so far - but once I click 'OK' on the Inputbox, the spreadsheet freezes and is forever thinking - and I inevitably have to close down the spreadsheet to continue working. The code is as follows;

Please Login or Register  to view this content.

Would anyone have any ideas on what is wrong with the above code, and/or a solution to alleviate the symptoms I described? Attached is a sample of the worksheet that the macro will be on.

All help is appreciated, and many thanks in advance.

**kev_** · 05-28-2017, 01:45 AM

Try

Set SrchRng = ActiveSheet.Range("F1", ActiveSheet.Range("F65536").End(xlUp))

**Reimertoad** · 05-28-2017, 02:23 AM

Thanks for the quick reply kev. While the change you suggested did hide the values I searched for, it still appeared to be continuing to search - and Excel ended up 'not-responding' as before. I tried this on several computers for the same outcome...

**bakerman2** · 05-28-2017, 02:43 AM

Try this one.

Please Login or Register  to view this content.

**jaslake** · 05-28-2017, 03:27 PM

Hi Reimertoad

Try using AutoFilter...

Please Login or Register  to view this content.

**Reimertoad** · 05-28-2017, 07:03 PM

Thank you all very much for your help! I've decided to run with bakerman2's option as it's working a treat and perfect for what's required. Thanks again and I appreciate your quick responses.

**leelnich** · 05-28-2017, 08:16 PM

For the sake of completeness, the problem with the original code was:
Repeated use of Find with no change in parameters. You started from the top every time and just kept finding the first instance over and over.
The FIX: use Find once. If it works, save the address, then loop using FindNext with previous result as starting point. Exit when first is found again.
NOTE: This works because FindNext wraps around to the top of the column when it reaches bottom. This allows you to start searching from any position in the range.

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

Issue with Macro Code to Search via InputBox and Hide

LinkBack

Thread Tools

Rate This Thread

Display

Issue with Macro Code to Search via InputBox and Hide

Re: Issue with Macro Code to Search via InputBox and Hide

Re: Issue with Macro Code to Search via InputBox and Hide

Re: Issue with Macro Code to Search via InputBox and Hide

Re: Issue with Macro Code to Search via InputBox and Hide

Re: Issue with Macro Code to Search via InputBox and Hide

Re: Issue with Macro Code to Search via InputBox and Hide

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to search several columns and then hide the row

[SOLVED] MACRO: If user clicks cancel in inputbox,then do nothing. Problem with inputbox appearance

[SOLVED] Urgent: Please help with application.inputbox issue

[SOLVED] InputBox issue

[SOLVED] Inputbox to search a value

[SOLVED] Macro ignores search code when 1 of the 2 search parameters is not found

[SOLVED] Search for Value from InputBox

Bookmarks

Bookmarks

Posting Permissions