Discontinuous ranges

[davemcguire]

hi all

im trying to select a discontinuous range within which my macro will run when changes are made.

here is my code

Please Login or Register  to view this content.

this works well upto column BE, whenever i want to go beyond there it returns an error.

any help would be appreciated!

[snb]

Please Login or Register  to view this content.

[davemcguire]

snb

thanks for that, but when i put that in the code it gives me this error message

"compile error:variable not defined"

and highlights the "cl" in your code, and the next line in mine.

here is the full code if it helps

Please Login or Register  to view this content.

apologies if im being completely dense!

[snb]

If you simply omit 'Option Explicit" it runs smoothly.

Please Login or Register  to view this content.

[davemcguire]

i must be being dense

i removed the "option explicit" and now im getting a "Next without For" error and dont know why.

if i remove the "next cell" command then it doesnt call the next instruction.

how do i get this working?

[mohd9876]

add end if before the next statement

[davemcguire]

There already is an 'end if' before the 'next' statement.

i really dont know whats gone wrong here, this code worked fine upto a certain no of ranges, and every suggestion leads to more errors, which leads me to believe there is something wrong with the code further down (as you guys are much better at this than me!)

can someone help me sort this out?

Please Login or Register  to view this content.

[mohd9876]

snb

thanks for that, but when i put that in the code it gives me this error message

"compile error:variable not defined"

and highlights the "cl" in your code, and the next line in mine.

here is the full code if it helps

Please Login or Register  to view this content.

apologies if im being completely dense!

Dear davemcguire,
in the code that you entered in this post you need another end if because you have to if statements; that is what i was talking about i will check your other code and get back to you if this doesn't work

[snb]

And please have a look in post #4

[davemcguire]

mohd - thanks for taking the time to look at 2 peices of code! if i add in a 2nd 'end if', it brings up a further error "end if without block if" !!

snb - also thanks for your help in this, i did see your code and added it in, however it doesnt call the mail script and essentially nothing happens. I'm fairly new to vba, and i dont understand your code in the slightest, whereas i wrote mine (mostly) and at least have a rough idea what does what within the code.

i think it may just be easier to limit the number of colums per worksheet!

[romperstomper]

Removing Option Explicit is not good practice or necessary. Try this version of your change event:

Please Login or Register  to view this content.

[davemcguire]

thats perfect romper, works a treat, thanks very much!

thanks to everyone else who replied too, its much appreciated!