Help with vba array

[michaelsmith559]

I need help with vba and array. Array's are hard for me to grasp, so need some help. I am wanting to input a large data set into an array. I want the array to remove the first three characters and return the results to a new sheet. In the example below, range(a1:e5) is the example data that would be put into the array and range(a7:e11) is how I would want the results. The data set is large from A1:K1048576 & L1:L568678. I have no clue how to put this into an array let alone work through it efficiently. Any help will be much appreciated. Thanks.

mm combinations.xlsm
	A	B	C	D	E
1	1, 2, 3, 4, 5	2, 7, 10, 41, 52	3, 15, 34, 64, 67	4, 43, 44, 50, 53	6, 18, 26, 49, 52
2	1, 2, 3, 4, 6	2, 7, 10, 41, 53	3, 15, 34, 64, 68	4, 43, 44, 50, 54	6, 18, 26, 49, 53
3	1, 2, 3, 4, 7	2, 7, 10, 41, 54	3, 15, 34, 64, 69	4, 43, 44, 50, 55	6, 18, 26, 49, 54
4	1, 2, 3, 4, 8	2, 7, 10, 41, 55	3, 15, 34, 64, 70	4, 43, 44, 50, 56	6, 18, 26, 49, 55
5	1, 2, 3, 4, 9	2, 7, 10, 41, 56	3, 15, 34, 65, 66	4, 43, 44, 50, 57	6, 18, 26, 49, 56
6
7	2, 3, 4, 5	7, 10, 41, 52	15, 34, 64, 67	43, 44, 50, 53	18, 26, 49, 52
8	2, 3, 4, 6	7, 10, 41, 53	15, 34, 64, 68	43, 44, 50, 54	18, 26, 49, 53
9	2, 3, 4, 7	7, 10, 41, 54	15, 34, 64, 69	43, 44, 50, 55	18, 26, 49, 54
10	2, 3, 4, 8	7, 10, 41, 55	15, 34, 64, 70	43, 44, 50, 56	18, 26, 49, 55
11	2, 3, 4, 9	7, 10, 41, 56	15, 34, 65, 66	43, 44, 50, 57	18, 26, 49, 56
Sheet3

 

[Legacy 143009]

I don't know if there is a more efficient way:

Sub test()
  Dim myArr1 As Variant, myArr2 As Variant
  myArr1 = Range("A1:K1048576")
  For i = 1 To UBound(myArr1)
    For j = 1 To UBound(myArr1, 2)
      myArr1(i, j) = Right(myArr1(i, j), Len(myArr1(i, j)) - 3)
    Next
  Next
  Range("A1048578").Resize(UBound(myArr1), UBound(myArr1, 2)) = myArr1
 
  myArr2 = Range("L1:L568678")
  For i = 1 To UBound(myArr2)
    myArr2(i, 1) = Right(myArr2(i, 1), Len(myArr2(i, 1)) - 3)
  Next
  Range("L568680").Resize(UBound(myArr2), 1) = myArr2
End Sub

 

[Alex Blakenburg]

I am finding your data ranges a bit confusing but given there are no other complications the below should be the fastest way to achieve the removal of the 1st 3 characters.

PS: It won't be best example for learning arrays though since it avoids looping.

Sub RemoveLeadingCharacters()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcArr As Variant
    
    Set srcSht = ActiveSheet
    Set destSht = Worksheets.Add(after:=Worksheets(Worksheets.Count))
    
    With srcSht
        Set srcRng = .Range("A1").CurrentRegion         ' Method Depends on sheet layout
        srcArr = srcRng
    End With
    
    Set destRng = destSht.Range("A1")
    
    With Application
        srcArr = .Replace(srcArr, 1, 3, "")             ' Remove 1st 3 characters
        srcArr = .Trim(srcArr)                          ' Remove leading space
    End With
    
    destRng.Resize(UBound(srcArr, 1), UBound(srcArr, 2)) = srcArr

End Sub

 

[michaelsmith559]

I don't know if there is a more efficient way:

Sub test()
  Dim myArr1 As Variant, myArr2 As Variant
  myArr1 = Range("A1:K1048576")
  For i = 1 To UBound(myArr1)
    For j = 1 To UBound(myArr1, 2)
      myArr1(i, j) = Right(myArr1(i, j), Len(myArr1(i, j)) - 3)
    Next
  Next
  Range("A1048578").Resize(UBound(myArr1), UBound(myArr1, 2)) = myArr1
 
  myArr2 = Range("L1:L568678")
  For i = 1 To UBound(myArr2)
    myArr2(i, 1) = Right(myArr2(i, 1), Len(myArr2(i, 1)) - 3)
  Next
  Range("L568680").Resize(UBound(myArr2), 1) = myArr2
End Sub

I tried the code but excel crashed and gave a out of memory error. So, I am going to try a different approach and start a new thread. Thanks for helping.

 

[michaelsmith559]

I am finding your data ranges a bit confusing but given there are no other complications the below should be the fastest way to achieve the removal of the 1st 3 characters.

PS: It won't be best example for learning arrays though since it avoids looping.

Sub RemoveLeadingCharacters()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcRng As Range, destRng As Range
    Dim srcArr As Variant
   
    Set srcSht = ActiveSheet
    Set destSht = Worksheets.Add(after:=Worksheets(Worksheets.Count))
   
    With srcSht
        Set srcRng = .Range("A1").CurrentRegion         ' Method Depends on sheet layout
        srcArr = srcRng
    End With
   
    Set destRng = destSht.Range("A1")
   
    With Application
        srcArr = .Replace(srcArr, 1, 3, "")             ' Remove 1st 3 characters
        srcArr = .Trim(srcArr)                          ' Remove leading space
    End With
   
    destRng.Resize(UBound(srcArr, 1), UBound(srcArr, 2)) = srcArr

End Sub

I tried the code but excel crashed and gave a out of memory error. So, I am going to try a different approach and start a new thread. Thanks for helping.

 

[Alex Blakenburg]

Like I said your data ranges don't really make sense. Are you really using all 1M + rows in Excel ?
My Excel instal is 64 bit handled the volume without any issues but I imagine that may not be the case for 32 bit instal.

At those volumes you really should be using Power Query and the data model.
Using all the rows in Excel would leave you at a significant risk of truncating your data (ie dropping rows beyond the 1,048,576)

 

[Legacy 143009]

@Alex Blakenburg is right. You should go for power query

 

[michaelsmith559]

Not sure what power query is. I'll look into it. I'm using excel 2013 also have 2007.

 

[Alex Blakenburg]

I'm afraid that you are using very old versions of Excel. Power Query it is standard from windows excel 2016 on.
In for 2010 it was an add-in and I am not sure about 2013 I think it might depend which type of licence you have.
PQ, Power Pivot & the Data Model and Power BI are all geared towards handling larger volumes of data. Even volumes exceeding Excel's total number of rows.

Here is a 9 min intro on Power Query from Leila Gharani:

 

[michaelsmith559]

I'm afraid that you are using very old versions of Excel. Power Query it is standard from windows excel 2016 on.
In for 2010 it was an add-in and I am not sure about 2013 I think it might depend which type of licence you have.
PQ, Power Pivot & the Data Model and Power BI are all geared towards handling larger volumes of data. Even volumes exceeding Excel's total number of rows.

Here is a 9 min intro on Power Query from Leila Gharani:

I'll look into downloading the power query add-in.