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

  • 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...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • 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...
  • 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#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top