LinkBack URL
About LinkBacks
Hi I'm faced with an error message when no cells are filtered for selection.
How can I correct the error by getting it to continue with the next worksheet?
Thanks in advance!
Please Login or Register to view this content.
Error when no cells are filtered for selection
Hi I'm faced with an error message when no cells are filtered for selection.
How can I correct the error by getting it to continue with the next worksheet?
Thanks in advance!
Please Login or Register to view this content.
Using SpecialCells will throw an error if there are no cells meeting the type specified you need to include error handling. You also need to declare your variables. Try this, note it doesn't select any sheets or ranges so should run more efficiently.
Please Login or Register to view this content.
Last edited by royUK; 01-04-2008 at 05:06 AM.
Hope that helps.
RoyUK
--------
For Excel Tips & Solutions, free examples and tutorials why not check out my web site
Free DataBaseForm example
hi RoyUK,
Many thanks for your response!
I've figured out how to resize the range with your help.
I can't post the table here (data sensitivity), but just to explain this is how the table structure is.
Col A & B: Two levels of Column Headings
Col C onwards: Jan to Dec Data (this depends on which month we are at)
2nd Last Col: Grand total
Last Col: Remarks Col (this is where I apply the filters and format the Data columns accordingly.)
The table starts at Row 3.
The code you posted works without any error... I've another Qn...
How can I filter with more criteria and format the corresponding cells accordingly?
Please Login or Register to view this content.
You can add criteria, use the macro recorder to get sample code whilst filtering the data. Post back for help adapting the generated code.
Hi this is the code i need to be adapted to the macro...
Please Login or Register to view this content.
Hi RoyUK,
There seems to be sth wrong when I run the macro.
For worksheets with no cells filtered at all, all the cells which are hidden by the filter still get formatted with the cell colour.
I've tried but am not able to understand why that's happening...
pls help
Your last code bears no resemblance to what I wrote for you. You can only use two criteria per column.
hi RoyUK,
I thot u had wanted me to use the macro recorder to get sample code whilst filtering the data and post back.
Yes, we can only use two criteria at any one time, which is why i repeated the step thrice.
Anyways, the code below now works for me. Its still nothing like wat you have originally written for me, cos for worksheets with no cells filtered at all, all the cells which are hidden by the filter still get formatted with the cell colour.
I know its not right, cos' I've not declared variables. Never understood how to do that. I've just been dabbling with VBA to see if it can help my workso this will do for now.
Many thanks for your help
Please Login or Register to view this content.
You can speed up your code by removing the selections.
I thought hat you wanted to Filter other Columns, thats why i suggested the Recorder.
You need to get to grips with variables if you want to code, try reading this;
http://www.excel-it.com/clear_code.htm
Try this I can't test it without some data.
Please Login or Register to view this content.
Last edited by royUK; 01-06-2008 at 01:43 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks