combining two codes to extract data instead of repeat the code twice

[MKLAQ]

Hi experts
I need combining two code instead of repeat the code again for different ranges
so the code import data from Prompt user to browse & Select file from folder . I have two ranges for two files when select from folder
first

 Sub ge_tdata()
 Dim ar As Variant, lRw As Long


 lRw = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row

 With Application.FileDialog(msoFileDialogFilePicker)
   If .Show Then
      With GetObject(.SelectedItems(1))
         ar = .Sheets("rp").Range("g2:i100").Value
        
         ThisWorkbook.Sheets("sheet1").Cells(lRw + 1, 2).Resize(UBound(ar), UBound(ar, 2)) = ar
        .Close 0
        End If
      End With
   End If
 End With
End Sub

second

 Sub ge_tdata()
 Dim ar As Variant, lRw As Long


 lRw = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Row

 With Application.FileDialog(msoFileDialogFilePicker)
   If .Show Then
      With GetObject(.SelectedItems(1))
         ar = .Sheets("rp").Range("A2:E100").Value
     
         ThisWorkbook.Sheets("sheet1").Cells(lRw + 1, 2).Resize(UBound(ar), UBound(ar, 2)) = ar
        .Close 0
        End If
      End With
   End If
 End With
End Sub

as you see there is different in ranges.
I have some notices if it's possible just select the folder and pull data from files without select files and when pull data should skip blank cells in ranges or make search for tha lastrow instead of specify the range every time .
I hope finding easy way to do that .

 

[MKLAQ]

I mean summing values in th column BUYING & SELLING for the same duplicated item in column B like this
for instance see th row2,3 how merge values for duplicated items

OUTPUT.xlsx
	A	B	C	D	E
1	ITEM	ID	BRAND	BUYING	SELLING
2	1	CCR1-CMB1	C-BM-100	353	109
3	2	CCR1-CMB2	C-BM-101	1356	12
4	3	CCR1-CMB3	C-BM-102	124
5	4	CCR1-CMB4	C-BM-103	123	2
6	5	CCR1-CMB5	C-BM-104	44	3
7	6	CCR1-CMB6	C-BM-105	55	4
8	7	CCR1-CMB7	C-BM-106	132
9	8	CCR1-CMB8	C-BM-107	154	1400
10	9	CCR1-CMB9	C-BM-108	88	12
11	3	CCR1-CMB13	C-BM-112	345	4
12	4	CCR1-CMB14	C-BM-113	456
13	5	CCR1-CMB15	C-BM-114	123	34
14	6	CCR1-CMB16	C-BM-115	5678	76
RP

 

[JEC]

Well I understood correctly. This should work

Sub jec()
 Dim dic, ar, a, x, x00, x01, x02, j As Long
 ar = Array("ITEM", "ID", "BRAND", "BUYING", "SELLING")
 x02 = "SELECT " & Join(ar, ", ") & " FROM `rp$`"
 
 With Application.FileDialog(4)
   If .Show Then
   
     With Sheet1.Cells(1)
        .CurrentRegion.ClearContents
        .Resize(, 5) = ar
     End With
     
     x00 = Dir(.SelectedItems(1) & "\")
     Set dic = CreateObject("scripting.dictionary")
     
     Do Until x00 = ""
       x01 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & .SelectedItems(1) & "\" & x00 & ";Extended Properties=""Excel 12.0"""
       With CreateObject("ADODB.recordset")
           .Open x02, x01
            a = .getrows
            For j = 0 To UBound(a, 2)
               If Not dic.exists(a(1, j)) Then
                  dic(a(1, j)) = Array(a(0, j), a(1, j), a(2, j), a(3, j), Val(a(4, j) & "_"))
               Else
                 x = dic(a(1, j))
                 x(3) = x(3) + a(3, j)
                 x(4) = x(4) + Val(a(4, j) & "_")
                 dic(a(1, j)) = x
               End If
            Next
           .Close
       End With
       x00 = Dir
     Loop
     Sheets(1).Range("A2").Resize(dic.Count, 5) = Application.Index(dic.items, 0, 0)
   End If
 End With
End Sub

 

[MKLAQ]

great ! just can you fix autonumbering in column ITEM 1,2,3 after merging ,please?

 

[JEC]

Yes, use the array like this

Array(dic.Count + 1, a(1, j), a(2, j), a(3, j), Val(a(4, j) & "_"))

 

[MKLAQ]

I really appreciate for your time and solution
have a great weekend !

 

[JEC]

You're welcome! You too

 

[MKLAQ]

Hi Jec
sorry ! suddenly, the code shows error in this line

.Open x02, x01

it informe me the sheet name rp is not correct despite of I make sure many times and using copy from your code and paste in the others files untile match the same sheets names without any spaces containing .
the headers and the sheets names are the same thing without any doubt .
this causes big headache , any solution for this dilemma,please?
if you need upload the files to see the problem I will that .
BTW your first code will brings the data and shows error and the second code doesn't brings data and shows error .
I hope helping me to find what's the problem.

 

[MKLAQ]

again sorry Jec !!! this is my bad !
main file should be out the folder when run the macro, I make mistake by make it inside the folder.
thanks