Hello,

This is my first post. I have two list of data and want to extract
continous range.
eg. 1 2
2 3
5 6
6 7
8 10

I want continous range like
1 3
5 7
8 10

I have to handle large data. Either formula or query is required.

Thanks,
Panneer selvam

I understand the 1-3 and possibly the 5-7, but how is 8 10 continuous ?
Also it is not 5-8 as continuous ?

Or are there other rules ?

NickHK

"Tamil" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> This is my first post. I have two list of data and want to extract
> continous range.
> eg. 1 2
> 2 3
> 5 6
> 6 7
> 8 10
>
> I want continous range like
> 1 3
> 5 7
> 8 10
>
> I have to handle large data. Either formula or query is required.
>
> Thanks,
> Panneer selvam
>

Panneer:

Is this what you want?

Option Explicit

Sub makelist()

Dim rSel As Range
Dim x1, x2
Dim lRowStart As Long, lRowEnd As Long
Dim lRow As Long, lRowAns As Long
Dim lCol1 As Long, lCol2 As Long

Set rSel = Selection

lCol1 = rSel.Column
lCol2 = lCol1 + rSel.Columns.Count - 1
lRowStart = rSel.Row
lRowEnd = lRowStart + rSel.Rows.Count - 1
lRowAns = lRowEnd + 3

x1 = Cells(lRowStart, lCol1)
x2 = Cells(lRowStart, lCol2)

For lRow = lRowStart + 1 To lRowEnd Step 1
If x2 <> Cells(lRow, lCol1) Then
Cells(lRowAns, lCol1) = x1
Cells(lRowAns, lCol2) = x2
lRowAns = lRowAns + 1
x1 = Cells(lRow, lCol1)
x2 = Cells(lRow, lCol2)
Else
x2 = Cells(lRow, lCol2)
End If
Next lRow

Cells(lRowAns, lCol1) = x1
Cells(lRowAns, lCol2) = x2

End Sub

> eg. 1 2
> 2 3
> 5 6
> 6 7
> 8 10
>
> I want continous range like
> 1 3
> 5 7
> 8 10

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"Tamil" wrote:

> Hello,
>
> This is my first post. I have two list of data and want to extract
> continous range.
> eg. 1 2
> 2 3
> 5 6
> 6 7
> 8 10
>
> I want continous range like
> 1 3
> 5 7
> 8 10
>
> I have to handle large data. Either formula or query is required.
>
> Thanks,
> Panneer selvam
>
>

Fantastic Mr.Martin,

Thanks a lot.

This is what I required.

You saved much time for my routine work.

Hats off to Google group.

- Panneer