Can a macro be used to unhide only a specific row that has been searched in a find?

[ibabs]

Hello,

Is it possible to use a macro that would unhide a row, if it comes up in a search?
I have a document that I am using now to automatically hide a row, once it is marked "done" in a particular cell.

The conundrum is if I ever needed to see the data in that cell again.

thanks
babs

[JasperD]

Yes you can.

Please Login or Register  to view this content.

Please click the * below if this helps

[ibabs]

Hi Jasper,

OK....since I have never used 2 macros at the same time, and I am trying to add yours to mine, (the one that initially does the hiding) this is what I have so far...., but its not working.. but I know its because of how I put them together...can you take a look and help me?

Please Login or Register  to view this content.

THANK YOU!
babs

[JasperD]

Hi,

A) why do you want to put them together?
B) your 'hide' macro is a worksheet_change macro... So it checks every time you make a change on your sheet if you typed 'done' - and if you did so, it'll make the row height to 0 pixels (which is actually something different from hiding the row)

You can't really add a find to a worksheet change routine (well, you can but in your current form I don't see the point)

Can you tell me specifically what it is you're trying to achieve and how you intend to do that? Thanks!

[ibabs]

Hello Jasper,

My mistake, I was having trouble trying to make a new macro. But I will work on it.

Basically, I have a bunch of sheets that hold data for different departments and I have a sheet called overview, that has a pasted Link option for all the other sheets in one place. This way, people can work on their sheets, and I can see the overview of each department. Problem was, when people started to delete rows in their sheets, the overview would get the #ref error each time. So, instead, I have it set that if a job is done or canceled, rather than delete that row, type "done" in a particular cell, and then the row would just hide in both the individual sheet, as well as , the overview sheet. That was my first macro ....

But then, I realized there were times I may need to go back and look at some of that old data, and there could be hundreds of hidden rows over time. So, I wanted to find a way to do a search that would find that hidden row, and then just have that row unhide in both the individual sheet and the overview if possible?

So, my intention was to first get the hiding part figured out, which seems to be working, but now I need to figure out how to unhide just one row in a search.

I hope that makes sense? But that is my goal here.

Any help or guidance is greatly appreciated!
Thank you.....babs

[ibabs]

Hi Jasper,
I think where I am stuck is, I originally pasted the code in the VB editor for the initial hiding whenever "done" is typed in a field and it hides that row and hides the row in the regular sheet and on the overview sheet.
Now, I am trying to find where to put the new code you so kindly provided. I tried making a new Module, but that didn't work either.
I was trying to find a way to upload my dummy file, but I guess you can't do that in this forum?
I figured that would help.....
Is there a way to upload files here?
thanks
babs

[ibabs]

HI Jasper,
I see what you are saying about the row height being 0...I gave you the wrong code! This is the one that works.

Please Login or Register  to view this content.

Is it possible to still attempt the unhiding of a found searched cell that is hidden?
thanks you!
babs

[JasperD]

Hi babs,

Sure - but I didn't take my computer today, so I'll write your answer tomorrow from work.
(It really sucks to write code from an iPhone....)

But basically I assume the situation is like this :

'Done' is typed in a specific cell or specific column to hide the row ...
If we can have you type in the search term you want to find in a specific cell, we can make it so once you typed something in that cell, it'll unhide the row on both sheets where that term is found.

This is what you're looking for, correct?

Which cells / column will 'done' be in and which. Cell do you want to be for the search term input?

Thanks

[JasperD]

Hi Babs -

Ok, so the code you have checks the sheet changes ; basically it lets you type "done" in *any* cell and then hides the row.
It works fine, but errors out on two occassions ;
1) if you select more than one cell and for example press delete
2) if you don't have a sheet named "overview"

I adjusted the code to tackle that.
Also, I put it so if you type something in cell A1, it will look for that term on the sheet and unhide the row if it's hidden.
You can adjust the A1 cell reference in your code to whichever cell you want to use as "search cell"
Also, when it unhides a row, it changes "done" to "un-done" in the row

Is this what you're looking for?

Please Login or Register  to view this content.

Please click the * below if this helps

[ibabs]

Hi Jasper,
I apologize for the late reply.
I just got a chance to try your code.
I replaced my existing code for the work sheet in the VB editor with yours and I first tried to test just putting done in a row.
I di get the following error...See enclosed snapshot. I am getting an error it seems in the search part, although, at this point, I was just going to try the "done" part to make a row hide and then I was going to search for something inthat hidden row, but got this initial error.
Any thoughts??
thanks!!!!!!!!!
babs

[JasperD]

Which excel version are you using?

[JasperD]

Please Login or Register  to view this content.

[ibabs]

Hi Jasper!

1. OK- I am using 2010
2. ALMOST THERE! The hide worked in the initial sheet and I was able to put a word in cell A1 and it came back undone!!

The only thing that didn't work, is when I hid the row in the initial sheet, it didn't hide it in the overview sheet (These are pasted as a link in the overview sheet, so when the row is hidden in any particular sheet, it has to hide in the overview).

I am looking at the old code and trying to figure out what made that work????

so, we are almost there!!!
thanks
thanks
babs

[ibabs]

Hi Jasper,
This is the line of code I think I need, but when I put it where it was before, it still didn't hdie the row in the overview sheet.
'

Please Login or Register  to view this content.

[ibabs]

HI Jasper,

hmm..I see the problem.

I only tested this with one dummy sheet and the overview sheet originally.

Once I made room in the first row, for the search box, the paste link was not in the same spot. and when I tried to add another sheet, it is the same problem.

Since the paste link seems to be doing and relative address, I am having a real problem. It only worked the first time cause both the copy and paste where in the exact same spot.

But, since I have about 8 sheets I am trying to put in the one overview sheet, the paste link will never be in the exact location as it was in the initial sheet.
is there anyway around this? I guess I assumed, as long as the word done showed up in a cell in the overview sheet, then it hid, but it was actually just the exact row number that was hiding and had nothing to do with the actual data....

I can not tell you how much I appreciate all your help...I feel it is so close!
babs

[JasperD]

A sample workbook would be wonderful (you can attach it, just zip it up£
Anyway, it's not that hard to solve, but : can you tell me which column the word 'done' would typically be typed in?
Is that likely to be in the first column...? Or fifth? Or twentieth..? What's the usual scenario?

I'm going to sleep now (00:30 here)
Will fix it for you in the morning

[ibabs]

Hi Jasper,
Good idea..I have been playing with it, but making a mess!!
I will get a dummy made up for you and send it to!
I will make a column for the done and I will tell you what it is when I send the file.
Good idea to get some sleep....I will send the file shortly and we will touch base tomorrow
babs

[ibabs]

Hi Jasper,

Here is a dummy file I made for you.

As a safety, I think it has t be column N for the "done", since we still may add a few more columns of info. This is still a work in progress...

There will be a few more tabs and lots of data, but this is a good representation of what I am trying to achieve.

I also took off the protection on the overview sheet (it is the only one that had any).
I don't want anyone to be able to change data in that sheet. Just be able to view, print, sort and filter. I wasn't sure if that would effect your code, so I took it off for now, but ideally, I would just want the overview to be protected so that people have to go into the proper tab and make any changes, including putting "done" in the proper place to hide the row. But, I figured I should take that off while we test.

So, here is the file....thanks again and we will touch base tomorrow!
babs