How to find out if rows are consecutive in a given range

mcomp72

Board Regular
I need to determine if the cells that a user selects are consecutive, in terms of the rows. I have been trying to write a function to determine this (and return either TRUE or FALSE), but I am having a very hard time figuring out how to do it.

I know how to get what cells the user has selected. I use Selection.Address. Let’s say that Selection.Address equals the following:

“$B$1,$C$5,$F$3,$C$2:$E$2,$D$4,$B$6:$B$8,$C$9:$E$9,$F$10:$F$14,$E$15,$C$15,$B$16,$C$17,$E$18”

Now I need to figure out if all the rows that are selected are consecutive. (In this instance, they are. There is at least one cell highlighted in Rows 1 – 18.) I cannot figure out how to write the code to determine it. One idea I had was to treat each range between the commas independently, getting the row(s) they include, and then combine the results in an array, and then somehow check the array to see if the numbers are consecutive. But I can't figure out how to do this. I'm also thinking there's probably an easier way that I'm just not seeing.

Any help would be greatly appreciated.
 

jasonb75

Well-known Member
Something like
Code:
If Application.Union(Selection.EntireRow, Selection).Areas.Count = 1 Then
    'rows are consecutive
Else
    'rows are not consecutive
End If
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top