Solved

Remove first column from a 2d array

Posted on 2014-12-12
145 Views
Last Modified: 2014-12-15
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
Question by:MacroShadow
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 2
4 Comments

LVL 51

Expert Comment

ID: 40495999
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
``````
Regards
0

LVL 51

Expert Comment

ID: 40496037
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)
``````
Regards
0

LVL 18

Accepted Solution

krishnakrkc earned 500 total points
ID: 40497806
``````    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)
``````
0

LVL 27

Author Comment

ID: 40498075
@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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlinâ€¦
Suggested Courses
Course of the Month9 days, 4 hours left to enroll

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.