Hiding Rows using VBA and the values within certain cells

[dannyking3107]

Hi all, apologies for duplication.

I am being driven slowly mad by my inability to get a simple piece of code working. I have a calendar that has days acroos the top and race series down the left. I want to be able to hide certain race series that I am not supporting this season by checking a box further down the sheet. For example, if I want to hide all of the rows that contain 'World Series', which is written in cell S161, I want to tick my check box next to this cell and have all rows that start with 'World Series' (in the A column range A1:A181) hidden. When the tick box is unticked, all the World Series rows are shown again.
This code currently stops and shows a compile error, Next without For. The tick box link cell is X161

I can supply the sheet if it helps, or a pdf of it so you can see what I am working on.

Please Login or Register  to view this content.

[Richard Buttrey]

Hi, and welcome to the forum,

Please note that your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Since you are new I'll edit it for you on this occasion.

Rather than use a loop to identify rows you wish to hide which can be extremely slow with large ranges, use Data Filter Advanced to filter the list in place on column A for the "s161" value, then have the macro delete all the filtered rows by using the SpecialCells(xlcelltypeVisible) to identify them. This is the fastest and most efficient method I know.

[royUK]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook question. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results..
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[dannyking3107]

Hi there,

Thankyou for responding, apologies for the post format.

I already tried the advanced filter, and it doesn't really get me the result I am looking for. It would be much more useful to me if I could hide rows that I specify. I am about to upload the file.

Danny

[dannyking3107]

Hopefully this is the file attached correctly...

2012 Events.xlsm

[Cheeky Charlie]

I already tried the advanced filter, and <<the way I did it>> it doesn't really get me the result I am looking for. It would be much more <<easy for me to understand>> useful to me if I could hide rows that I specify.

My point is that advanced filter works very well, once you've learned how. I recommend you avoid mandating how the solution is delivered, just explain what you need to achieve.

One question (following your upload) did you want to delete the hidden rows or just temporarily hide?

CC

[dannyking3107]

Hello there CC,

I would like to only hide the rows. The plan is that as circumstances change I can look at different series at a time.

[Cheeky Charlie]

Good, that was what I thought. Irritatingly, my company firewall prevents me from downloading your attachment, but I think your code should be as simple as:

Please Login or Register  to view this content.

[Cheeky Charlie]

Good, that was what I thought. Irritatingly, my company firewall prevents me from downloading your attachment, but I think your code should be as simple as:

Please Login or Register  to view this content.

[dannyking3107]

Hi again,

I have revised the code a little, no it is just stalling on End If statements, so it must be a syntax error on my part, it seems to me that I am almost there - can someone help me just finish the job off?

Please Login or Register  to view this content.

[dannyking3107]

Sorry CC,

I think we crossed each other, I have read your reply now, and pasted the code in, but it stalls on an expected End With.

I would ideally like to be able to make the selection dynamic, so instead of "World Series" as the lookup string, I would like to be able to define the contents of a cell - does that make it impossible?

Thankyou for your help to this point, I am really grateful

Danny

[Cheeky Charlie]

This is your code corrected for syntax, but the approach is not good, as explained:

Please Login or Register  to view this content.

NB using indentation effectively makes it much easier to see your missing closing statements.

HTH

[dannyking3107]

Dear CC,

It really does, I understand a lot better what is happening now. The only problem I have now is that when I run the macro, it stalls with a run time error 424, says Object required and highlights this line:

Please Login or Register  to view this content.

Again, not sure how to solve that?

Danny

[dannyking3107]

Further, I am doing something else on the same sheet now because the checkboxes Excel puts in are too small and cannot be resized. But I get the same error, Error 424, Object Required. So I wonder, is this an Excel (VBA) settings thing, or am I just being unlucky. The code is:

Please Login or Register  to view this content.

I am so nearly there, please don't give up on me!!

Danny

[dannyking3107]

For anyone reading this thread and needing to know the answer (like me at one stage!), it is below. I haven't got an answer to the checkbox code yet, but hopefully soon will.

Please Login or Register  to view this content.

Bit disappointed that the help dried up and I had to go elsewhere, but here is a solution for the next poor soul. Happy programming!