1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by Keebellah, Apr 15, 2010.

Thread Status:
Not open for further replies.
  1. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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 :)
     
  2. Keebellah

    Keebellah Trusted Advisor Thread Starter

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    (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
     
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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/917051

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice