Solved: Excel: need vba code to determine if selected range is contiguous

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
I would like to know if anyone has a simple macro that returns a true if a selected range is not contiguous.

The idea is, I have a workbook with one sheet for every month where tasks, vacation etc are kept and planned.
You can select a range or several ranges (see attached bmp file) and usign the Right mouse button it permits the user to enter a 'V' for vacation in that selected range or ranges

What I am looking for now is if in case the range is not contiguos I want to be able to determine where each different range starts and ends (that I can do with some code I already have for a contiguous range) but now I would like the code to return the three anges

"A4:B5" "C6:E7" "F8:J10"

Then I execute the appropiate code to enter, format or whatever each cell in the separate ranges, the actions will be same but skippng the not selected cells.

Maybe something like not in selection?

It's difficult to attach the workbook I'm using since I don't have an empty one at hand right now, but I'm preparing one.

It's easier to prgram than to explain what I want.

Thanks in advance for any tip :)
 

Keebellah

Hans
Thread Starter
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
(y)Just for the info, I FOUND IT! :) :) :)

I found the following code: it selects one cells of the cells in de selected non cntiguous range

Sub UnSelectActiveCell()
Dim Rng As Range
Dim FullRange As Range

If Selection.Cells.Count > 1 Then
For Each Rng In Selection.Cells
If Rng.Address <> ActiveCell.Address Then
If FullRange Is Nothing Then
Set FullRange = Rng
Else
Set FullRange = Application.Union(FullRange, Rng)
End If
End If
Next Rng

If FullRange.Cells.Count > 0 Then
FullRange.Select
End If
End If
End Sub

Now I added my code to loop until count < 1

Sub Unselectthem()
Do While Selection.cells.Count > 1
UnSelectActiveCell
Loop
Range("B5").Select
End Sub

and in the first one I will replace the red text with my code

If FullRange.Cells.Count > 0 Then
FullRange.Select <<< here my code to invoke the necessary module
End If

I just added this, maybe somebody else is / was looking for something similar.

Thanks anyway. :p
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top