sorting non-contiguous data

[fando]

I have a set of non-contiguous data and I would like to sort the ranges of contiguous data into a range label in a new worksheet (excel) or table (access)

Example:
11
12
13
15
16
17
18
21
22
23
24

Desired Output:
11-13
15-18
21-24

 

[Loomah]

Hi fando
Assuming your data is contiguous but not sequential this should do the trick. It assumes your data starts in A1 and will create your second list in columns C.

Sub mit()
Dim lRow As Long
Dim iFirstNum As String
Dim iLastNum As String
Dim lCount As Long

iFirstNum = Range("A1&quot
iLastNum = Range("A1&quot
lRow = 1
For lCount = 1 To Range("A1&quot.End(xlDown).Row
If Cells(lCount + 1, 1) = Cells(lCount, 1) + 1 Then
iLastNum = Cells(lCount + 1, 1)
Else
Cells(lRow, 3) = "'" & iFirstNum & "-" & iLastNum
lRow = lRow + 1
iFirstNum = Cells(lCount + 1, 1)
End If
Next
End Sub[/code]
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[fando]

Thank you very much!