My data is in A2 to Z500. Column A, stating at A2 to A500 are numeric amounts representing quantities. I'd like to have a "Button" or such in cell A1 when clicked the user would be prompted "What quantities would you like removed from the list?" Based on what number the user enters, those rows would be hidden.
A bonus would be then in cell B1 a note would appear "Rows with quantities xx are now Hidden"
Thanks fro any direction - not too complicated solution hopefully.
-j
A partial solution would be helpful.
j
I haven't yet figured out how to enter an array, but I'm working on it
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Thanks for anything you can do.
If we can figure this out using a recorded macro that is fine with me.
Last edited by JET2011; 07-07-2017 at 11:15 AM. Reason: typo
You'll probably want to play with this button:
And, I had something grander in mind but this seems to work!
Please Login or Register to view this content.
Please Login or Register to view this content.
That looks interesting.. I'll need a little detail on how to exactly implement it. Thanks for your effort, looks like it took some work.
-J
Just run the recorded macro to place the button (I wasn't very fussy)
The HQH routine goes in a standard module:
Directions for running the routine(s) just supplied
If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)
Then - Copy the code to the clipboard
Open your Workbook
Press ALT + F11 to open the Visual Basic Editor.
Select "Module" from the Insert menu
Type "Option Explicit" then paste the code under it
With the cursor between Sub and End Sub press F5 (F8 to Single Step)
OR
Press ALT + Q to close the code window.
Press ALT + F8 then double click on the macro name (just push the button)
*Save the book as macro-enabled
Last edited by xladept; 07-07-2017 at 12:48 PM.
I actually have it working - not sure how I even did it. Very happy to see it working. Little problem - I did not state my issue so clear, what I really need is it to hide the quantity entered by the user and also all the values below that number too. IE User enters 10, all quantities 10 and below are hidden. Another issue - how do I undo it? I tired entering o and it collapsed all my header rows. That was scary - I figured out how to run it (because the button was now hidden too) and entered 1 but I really don't want any hidden for the moment.
Thanks again this will be very cool in the end for sure.
-j
Did you only want to be able to enter one number? The way it is now, there's no limit.
You reset it by entering a null - just hit the enter key.
*Each entry separated by a comma
Last edited by xladept; 07-07-2017 at 05:36 PM.
One number would be ideal. I did try 1,10 and it hid 1 and 10. I tried 1-10 and hid the same 2. The best would be enter 10 and 10 and below get hidden. A range be second best, say enter 1-10 which would hide 1-10.
The null entry worked nicely and I liked how you added the little comment to tell the user what rows are hidden.
Thanks for everything.
Regards,
-j
Last edited by JET2011; 07-10-2017 at 10:42 AM.
This is your preference:
Please Login or Register to view this content.
Works - sweet! I have a need to move my data to column F, first data at F6 last at F448. Maybe I'll try to figure it out, wont be easy for me.
Thanks again the is excellent!
J
Just change:
toPlease Login or Register to view this content.Please Login or Register to view this content.
And - thanks for the rep!
Last edited by xladept; 07-10-2017 at 05:37 PM.
Its really great and thanks for your continued attention. Is this Cells (r, 6) change in just one place or the 3 times it appears? Also I don't want any hidden rows 1-5 so change the For r = 2 to r = 6. Seems to work fine as is.
HTML Code:
Everywhere it appears. By all means, if your data starts in row 6 the the loop should start at row 6 - or whatever
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks