Hello, I need some help. I never went to school for excel, more of a DIY person. Sorry in advance for lack of knowledge.
I have a range formula that worked, but once i added another range it, it only picks up the second range now and not first range. I need it to filter out both columns. Example: if cell A4 has value but Q4 is blank, Row is hidden and vise versa.
Thanks
Please Login or Register to view this content.
Can you explain what the code is supposed to do?
Entia non sunt multiplicanda sine necessitate
Please Login or Register to view this content.
Your problem (as far as I understand) is that you are redefining your r variable from the A column to the Q column. You either need to define it as a combined range, or set another variable for the q column and loop through both ranges. You can't define r as column A and then define it as column Q, otherwise it will only search Q.
Maybe this:
Please Login or Register to view this content.
Hi,
You have only dimmed one r range. All you're doing is first setting it to A4:Q62 and then immediately resetting it to Q4:Q62. Doing it your way you'd need to Dim another range - say r2
However you should avoid loops wherever possible since they are inefficient and slow where large ranges are involved. The fastst and most elegant way of doing stuff like copying, deleting, hiding..etc certain rows is to apply an Autofilter and then use a single line of code like:
Formula:
Please Login or Register to view this content.
I'd be inclined to add a helper column to identify the rows to hide. e.g.
Formula:Please Login or Register to view this content.
and filter for the word "Hide" on the helper column.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Try this.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Its a schedule/calendar for work. Column A are employees, if the name is not in column a the row is hidden. Column Q represents the work weeks, some months have 5-6 weeks on the calendar month (Dec 2018 takes 6 rows in the calendar), depending if the month starts on a Saturday or Sunday, so it eliminates the excess weeks or add the extra week in.
yah its not needed, but i enjoy making excel codes, its fun.
Norie, please correct me if I'm wrong - would that code not only hide if the entire row was blank?
This is not meant as a slight - I am learning consistently and I try to understand everything I read in other's suggestions.
No, it only checks the values in 2 cells - the values from column A and column Q of the current row.
I believe the OP wants to hide the rows if Cell in Column A or Cell in Column Q is blank.
if cell A4 has value but Q4 is blank, Row is hidden and vise versa.
I suspect you're right Dave, That's what I had assumed although I must admit the and vice-versa stumped me.Originally Posted by davesexcel
Does that mean that the row is hidden
if A4 has value and Q4 is blank, or A4 is blank and Q4 has value
or the row is hidden
if A4 has value and Q4 is blank but NOT if A4 is blank and Q4 has value
Sorry Norie I should have clarified - your code only hides the row if the cells in both A and Q are blank?
Thanks for the help, I haven't got it to work yet, most likely newbie error lol.
So far with the new codes it still doesn't hide all the correct rows.
The ideas is
Visible Rows are - Cells in Column A & Q with values
Hidden Rows are - Cells in Column A and/or Q with no value, get hidden.
I did have this code working, but its completely different from the first. But i'd prefer the the one posted above.
The issue was, It only refreshed when the changes were made on the sheet itself, but i wanted it to be able to refresh when changes were made on another sheet, the main sheet where everything is inputted.
That's why i was using the code above, it was able to refresh when changes were made from my main page sheet. Plus i was trying to have less clutter with codes and it to be more seamless.
week 1 is row 4-12
week 6 is row 53-62
I been battling this for a while now, many different attempts. But ill take any option that makes it work. thanks again for the help.
Please Login or Register to view this content.
Attaching a sample workbook enables others to work on your problem:
To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.
Click on GO ADVANCED and click "manage attachments" to open the upload window.
To add a file to a post
Try this.
Please Login or Register to view this content.
Here is my workbook, remember i have no schooling on this, so no laughing.
As suggested in my post #6 put the following formula in AI4 and copy down to AI52
Formula:Please Login or Register to view this content.
Now your sheet activate event is
see attachedPlease Login or Register to view this content.
Thanks you for the help, it works perfect. I understand what your previous post meant now.
Much appreciated.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
,
Bookmarks