I have a macro that will hide a row based on a cell's value inside that row. The problem is, it seems to take a very long time to perform this task. I know our work machines here are slow but is there anything that can be done to the code below to speed it up?
Screen updating and events are turned off.
Please Login or Register to view this content.
"Hide_Rows_Test" =Gen_3!$CB$17:$CB$626
Last edited by PY_; 03-04-2011 at 10:39 AM.
The problem is probably because you are looping through the range & hiding individual rows, it's generally quicker to create a range of the cells & then hide.
What value are you looking for?
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
Basically within the range: If the value = "" then hide row, If the value = "x" then keep unhidden.
This saved me 6 seconds off my time (according to a Now() calculation before and after each code). But how can i make it work with a named range so the numbers change as the range grows (when i add more to the worksheet)
Code above = 1:38 to run
Code below = 1:32 to run
Please Login or Register to view this content.
Last edited by PY_; 03-02-2011 at 02:37 PM.
If your data is in a table format then use AutoFilter, see my code here
With the way it is all set-up ,it's not in a table so unfortunaltely filtering will not work. It looks like i am stuck with the way it runs now. Shaving 6 seconds off the total time does help somewhat though. Ill just have to remember that if i add data to the worksheet, i need to adjust that number in my vba.
Sounds like it would be worth the effort to design an efficient spreadsheet
hi, may be this way will help you to speed up hiding rows, please check attachment, run code "test"
I would if i could roy. The way this worksheet is layed out i am unable to do so.
Thanks waterserv, will check it out this evening.
I am guessing it is grouping all rows with a "2" in column "A" together and then hiding those grouped rows.
I am not familar with how it is typed so i am unable to edit it. I know this is a dumb question but: Can you explain how i change from looking in row A to looking in row C for example?
Nevermind, i got it figured out. Now to test it.
Last edited by PY_; 03-03-2011 at 08:02 PM.
I have attached a stripped version of my worksheet here. Watersev, i get a "Method 'Range' of object '_Global' failed" on this line:
.Please Login or Register to view this content.
It could be because I messed up when adjusting your code but im not sure.
Edit: Pasted the wrong worksheet + code in my post. It has been corrected.
Last edited by PY_; 03-04-2011 at 09:51 AM.
hi, PY_, what rows should be hidden, marked to hide or to leave visible and hide all the rest?
Rows with "x" stay visible. Rows with blank cells, hide.
I can change this however is best but i am only looking within a specific range.
Last edited by PY_; 03-04-2011 at 09:23 AM.
please check attachment, code "watersev1"
watersev,
That code is truly amazing compared to the ones i had! Check out the times on an actual worksheet run:
Hide_Accessory_Rows - took 2:48
i_Hide - took 2:24
watersev - took 0:02
Thank you so much for the help!
you are welcome
Wow Watersev this macro is awesome!!!
Can you help me with a similar situation?
I need to hide columns instead of rows, and in my case the range is from column G to column BD.
In the row number 6 I have numbers
If the number in this cell is 0 the column has to be hidden
I have tried to modify your macro, but my macro skills are not so professional.
Thank you in advance!
Manolo
@poldark:
Please read and adhere to these simple rules!
1. ....
2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.
Ben Van Johnson
Ok I will proceed as you indicate.
Sorry for the error. I am new here
Thank you very much for this excelent forum!
Manolo
Hi All,
I have a data sheet (AGNT) for all sales made by an Agent identified by the country.
I have made a report in the next sheet (CTRY), whereby on the country selection the values will get updated only for the agents for that particular selected country.
I wanted to hide the cells & only populate the cells which match the selection and have values.
How can i do this with a Macro?
Attaching the same for your reference.
Bokishai,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Can u help me posting a new thread for this question?
Click on the "Start New Thread" button
Hi,
When I tried to reuse the code from "TestHideRows"
I get this: error:'1004': Method 'Range' of object '_Global' failed.Please Login or Register to view this content.
What am I doing wrong?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks