VBA- To Select a range based on repeated values from Order id

[Stranger8421]

Hey Guys,

Need one small help from you. Need to select an Order_Num range and cust_Num range from the given below sheet.

Order_Num	Date	Cust_Num	Order_range	Cust_range
141​	09/21/2022	C123
141​	09/21/2022	C123
146​	09/21/2022	C125
146​	09/21/2022	C125
146​	09/21/2022	C125
146​	09/21/2022	C127
148​	09/21/2022	C127
148​	09/21/2022	C127
148​	09/21/2022	C129

So In this First Order id - 141 its have two lines and cust_num for Order id is also have same Cust_num. So Order_range in D3 is "A2 to A3" and Cust_range in E3 is "C2 to C3"
2nd Order id - 146 its have four lines and cust_num for Order id have two cust_num. So Order_range in D7 is "A4 to A7" and Cust_range have two cust_id so in E5 is "C4 to C6" and in E6 range is "C7 to C7".
3rd Order id - 148 has 3 lines and two cust_num for the same. And Order_range in D10 is "A8 to A10" and Cust_range in E9 is "C8 to C9" and in E10 is "C10 to C10"

So the final Sheet should be like:-

Order_Num	Date	Cust_Num	Order_range	Cust_range
141​	09/21/2022	C123
141​	09/21/2022	C123	A2 to A3	C2 to C3
146​	09/21/2022	C125
146​	09/21/2022	C125
146​	09/21/2022	C125		C4 to C6
146​	09/21/2022	C127	A4 to A7	C7 to C7
148​	09/21/2022	C127
148​	09/21/2022	C127		C8 to C9
148​	09/21/2022	C129	A8 to A10	C10 to C10

I have written a code till the Order_range selection. My code:-

Private Sub CommandButton1_Click()

Dim ws1 As Worksheet, rng As Long, lastrow As Long, FirstInvoice As String, Count As Long, A As Long, Count1 As Long
Dim intComp As Integer, B As Long, intComp1 As Integer, FirstShip As String, C As Long, C1 As Long

Set ws1 = Sheets("Sheet1")
lastrow = ws1.Range("A:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

FirstInvoice = Cells(2, "A").Value


If FirstInvoice <> "" Then
Count = 1
Count1 = 2
End If

C = 1
C1 = 2


For A = 2 To lastrow + 1


intComp = StrComp(FirstInvoice, Cells(A, "A").Value)

If intComp = 0 Then

Count = Count + 1

Else

' MsgBox (" First invoice range from " & Count1 & "to range = " & Count)
ws1.Cells(A - 1, "D").Value = "A" & Count1 & " to " & "A" & Count


'''Start- Enter here for Cust_range selection


'''End- Enter here for Cust_range selection
Count = Count + 1
Count1 = A
FirstInvoice = Cells(A, "A").Value

End If

Next A

End Sub

My Code Output:-

Order_Num	Date	Cust_Num	Order_range	Cust_range
141​	09/21/2022	C123
141​	09/21/2022	C123	A2 to A3
146​	09/21/2022	C125
146​	09/21/2022	C125
146​	09/21/2022	C125
146​	09/21/2022	C127	A4 to A7
148​	09/21/2022	C127
148​	09/21/2022	C127
148​	09/21/2022	C129	A8 to A10

So could you please help me to select Cust_range.

Regards,
Joe