Code to Cover Non-Contiguous Columns

[cmpgeek]

hey yall,
i found a VBA code in the KB last week that was the answer to my prayers...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=75

( thanks Steiner)

I had just created a spreadsheet with about 60 sheets and 6 columns per sheet of nothing but cells to enter times. Steiner's code has made it so that all i have to enter is 2345 and it formats it to 23:45. i know how trivial that may sound, but when you are entering 4 or 5 times on each of those 60+ sheets, it can save you a lot of time if you are accustomed to using the number pad and you have to stop each time.

Anyway - i digress - my new problem is that the other two files i want to use this code on are not as simple (in as far as the basic code fixing it)

My monthly census report has rows that are nothing but times, and my monthly volume report has columns that are strictly for times. However - neither the rows in the census report, nor the columns in the Volume report are adjacent to all the others.

The code in Steiners KB submission uses this line:

If Intersect(Target, Range("H:L")) Is Nothing Then Exit Sub

i figured out how to get it to at just one column, but i can not figure out how to get it to look at several columns on the same sheet that are not adjacent to each other. and the same is true for having it look at non-adjacent rows...

Does anyone have any suggestions?

thanks in advance

[tommy bak]

Hi
Try this

Dim x As Range
Set x = Union(Range("H:J"), Range("P:P"), Range("A:A"))
If Intersect(Target, x) Is Nothing Then Exit Sub

[TonyJollans]

Hi cmpgeek,

You will need to check whether the target is in each contiguous range separately, something like ..

If Intersect(Target, Range("G:H")) Is Nothing Then _
    If Intersect(Target, Range("L:L")) Is Nothing Then Exit Sub

This will do it for columns G, H and L. I'm sure you can adjust for others

[cmpgeek]

yall are the greatest! i will try them ASAP!

thanx!

[tommy bak]

Hi cmpgeek
Just saw that you also mentioned rows.
[VBA]

Dim rng As Range
Set rng = Union(Range("H:J"), Range("P:P"), Range("A:A"), Range("3:3"), Range("5:5"))
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox "Hit"
[/VBA]

BR
Tommy Bak

[cmpgeek]

Tommy - can you explain this coding to me a little bit? the spreadsheet that i mentioned where i wanted the code to work on the rows did not have columns that would need the code - just rows... would i then leave out the ranges where you listed column names and just leave the ones where the row names are listed?

thanks for your help...

Hi cmpgeek
Just saw that you also mentioned rows.
[VBA]

Dim rng As Range
Set rng = Union(Range("H:J"), Range("P:P"), Range("A:A"), Range("3:3"), Range("5:5"))
If Intersect(Target, rng) Is Nothing Then Exit Sub
MsgBox "Hit"
[/VBA]

BR
Tommy Bak

[tommy bak]

Hi cmpgeek

Yes, you can just leave out the columns. No problems..
in range-rng you set any cells, rows ang column you like
Union means putting the areas together and intersect returns nothing if Target and rng does not intersect.

[VBA]
'Here you set the cells where you want something to happen
'you can use single cells, multiple areas, entire columns or rows
' In this case I use row 3 and row 5
'
Set rng = Union(Range("3:3"), Range("5:5"))

'If the Target cell is not within rng then leave

If Intersect(Target, rng) Is Nothing Then Exit Sub
' if the target is within rng then....
MsgBox "Hit"
[/VBA]


br
Tommy Bak

[cmpgeek]

thanks! you are great!