VBA Stopped Working

Confusing....

What are
Private Sub Worksheet_SelectionChange_DI_75T(ByVal Target As Range)
Private Sub Worksheet_SelectionChange_DI_35T(ByVal Target As Range)
Private Sub Worksheet_SelectionChange_DI_Mate(ByVal Target As Range)

?

So, you want the whole code into one Selection_Change event?

No, you can't do it so...
Perhaps...

Please Login or Register  to view this content.

Correction

Please Login or Register  to view this content.

You have clear regularity between columns to loop and columns to hide.
As your loop column index increases by 2 (A, C, E, G etc.), hide column increases by 58 columns.

Backslash operator in calculation gives you round down integer.
e.g
5 \ 2 = 2, which you can also rewrite like Application.RoundDown(5/ 2, 0), or Fix(5 / 2)

So, as loop column increases by 2, the result from that calculation will increase by 1.

-4, -7 in calculation are the adjustment for the hide column index.

I added the variable "myAdj", so you can do a step debug.

Please Login or Register  to view this content.

HTH

It runs fine here.
Is any sheet protected?

If protected, add 2 lines

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Still comes up with same error

Many thanks

OK, try this one then

Please Login or Register  to view this content.

OOps,of course...
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

I'm sorry,

This is now working

However,
When selecting a cell, it now jumps to the selected worksheet and asks for the password.
If I protect the sheet without a password, the screen flicks to the corresponding sheet then back again briefly.

Many thanks

If only one password for all the sheet

change to

Please Login or Register  to view this content.

Change password to suite

Awesome, that solves the asking for password issue.

However when selecting a cell, the screen is flickering to and from the worksheet accordingly
If for example I press down arrow to move down 5 cells, the screen flicks to and from 5 times.

Is there a way to stop this please?

Many thanks

Then add 2 lines

Please Login or Register  to view this content.

Cool, getting there

Your help is really appreciated!

Now when selecting cells, the sheet has to think for a second or 2 before I can do anything.

Resolvable?

Many thanks

I don't think so.
If they are the rows to be hidden, autofilter could be applied.

However, it can be narrowed the range of Selection.
The current code will run for each one of the cell regardless of the cell selected, but if it limit to the change within the fixed range, it can be much faster.

I've found that if I leave the password blank to protect the sheet, it works so much faster and more acceptable.

Many thanks for your assistance Jindon, very much appreciated
+1 to your reputation

You are welcome and thanks for the rep.

Sorry,
Think I'm being dumb

Can't get sheet9 to work

Please can you tell me where I going wrong:

Please Login or Register  to view this content.

Many thanks

Hi
Your range is up to col.S, which is column index of 19.

Please Login or Register  to view this content.

So it will never go in to Case Else

Please Login or Register  to view this content.

Even that took a moment to register an understanding lol.
I was looking at it referring to sheet9 being the problem, not the source list on the main sheet in question.

I have now edited the code accordingly and believe I now have it fully functional:

Please Login or Register  to view this content.

Beginning to make more sense.

Thankyou Thankyou Thankyou

OK, one thing though.

Subscript of Range must be less than 255 characters.

Please Login or Register  to view this content.

If you ever need to extend the range and exceed the limit, use Union method like

Please Login or Register  to view this content.

That makes sense,

In my case, I've split it up into categories, so to make things more simplified, I've applied the ranges into those categories:

Please Login or Register  to view this content.

Many thanks

Something strange has happened

This script has been working perfectly until all of a sudden recently

All of a sudden I'm getting the following error and one of the lines when I go into debug highlights yellow:

error.jpg

I have confirmed the sheet is unprotected.

Please Login or Register  to view this content.

Any ideas please ?

Many thanks

Can not replicate the issue,

If the cell value possibly have Error then change to

Please Login or Register  to view this content.

Hmm,

This has really baffled me...

I was trying to delete parts of the spreadsheet so I could send the file to here for you to see, (still not succeeded in that lol), when I went though each sheet and deleted the comments.

Once all comments were deleted, I noticed the script then working again, behaving as designed.

After a bit of digging, I found that there is a comment on Sheet5 Cell GB18, which all it says is 'at Alsbury', delete this comment on its own, (leaving all the others in place), and the script is working.

Strange thing is, if I delete the comment and re-insert it (even with using copy/paste), the script carries on working.

The additional lines in the script above did not solve the problem unfortunately.

Still can not replicate the issue here.

Have deleted as much as I can with error in place

File attached.

Many thanks.

See if this works,

Please Login or Register  to view this content.

For some reason, this is taking ages when clicking in a different cell
Circle busy icon goes round for quite some time before the cell becomes usable.

Not so bad on the file attached here, but in the original, is taking 30-60secs.

many thanks

Cool, That's working

When returning to Setup sheet, I see something going on in bottom left of screen but can't make out what its doing lol.

Fault has now gone and script is working again.

Many thanks for your assistance, Very much appreciated.