[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 171
  • Last Modified:

Remove first column from a 2d array

Is it possible using VBA only (no copying to excel etc.) to remove the first column in both the the first and second dimension?

Sample array
MyArr(1, 1) =  "Apple"
MyArr(1, 2) =  "Bannana"
MyArr(1, 3) =  "Pear"
MyArr(1, 4) =  "Orange"
MyArr(1, 5) =  "Cherry"

Desired result
MyArr(1, 1) =  "Bannana"
MyArr(1, 2) =  "Pear"
MyArr(1, 3) =  "Orange"
MyArr(1, 4) =  "Cherry"
0
MacroShadow
Asked:
MacroShadow
  • 2
1 Solution
 
Rgonzo1971Commented:
HI,

pls try

Option Base 1
Sub Macro()

myArr(1, 1) = "Apple"
myArr(1, 2) = "Bannana"
myArr(1, 3) = "Pear"
myArr(1, 4) = "Orange"
myArr(1, 5) = "Cherry"
For i = 1 To UBound(myArr, 2) - 1
  myArr(1, i) = myArr(1, i + 1)
Next i
ReDim Preserve myArr(1, UBound(myArr, 2) - 1)

End Sub

Open in new window

Regards
0
 
Rgonzo1971Commented:
And why not simplify

myArr(1) = "Apple"
myArr(2) = "Bannana"
myArr(3) = "Pear"
myArr(4) = "Orange"
myArr(5) = "Cherry"
For i = 1 To UBound(myArr) - 1
  myArr(i) = myArr(i + 1)
Next i
ReDim Preserve myArr(UBound(myArr) - 1)

Open in new window

Regards
0
 
krishnakrkcCommented:
    Dim x, y, i As Long, MyArr()
    
    ReDim MyArr(1 To 2, 1 To 5)
    
    MyArr(1, 1) = "Apple"
    MyArr(1, 2) = "Bannana"
    MyArr(1, 3) = "Pear"
    MyArr(1, 4) = "Orange"
    MyArr(1, 5) = "Cherry"
    
    MyArr(2, 1) = "Apple"
    MyArr(2, 2) = "Bannana"
    MyArr(2, 3) = "Pear"
    MyArr(2, 4) = "Kris"
    MyArr(2, 5) = "Sam"
    
    x = Evaluate("Row(1:" & UBound(MyArr, 1) & ")")
    y = Evaluate("transpose(Row(2:" & UBound(MyArr, 2) & "))")
    MyArr = Application.Index(MyArr, x, y)

Open in new window

0
 
MacroShadowAuthor Commented:
@Rgonzo1971
 It cannot be simplified because the array is populated from a range (MyArr = Sheets(1).Range("A1:G56").Value) which always creates a 2d array.
MyArray may contain several rows, your code won't work in such a case.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now