Solved

Remove first column from a 2d array

Posted on 2014-12-12
4
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
Comment
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

by:Rgonzo1971
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

Open in new window

Regards
0
 
LVL 51

Expert Comment

by:Rgonzo1971
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)

Open in new window

Regards
0
 
LVL 18

Accepted Solution

by:
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)

Open in new window

0
 
LVL 27

Author Comment

by:MacroShadow
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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…

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.

Join & Ask a Question