1

So let's say i have and 4x4 array of various numbers.

I want to delete the third array column, and switch positions of the second and fourth columns within the array.

The ultimate goal is copying information from a sheet into an array, and prepping the array to paste into another sheet.

How would I be able to do this?

Sub test()
Dim Arr as variant
Arr=Sheets("Worksheet").Range("A1:D4")
'Delete third column
'Switch second and what was the fourth but now is the 3rd column.
Sheets("Sheet2").Range("A1").Resize(UBound(Arr, 1), UBound(Arr, 4)) = Arr
End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
M B
  • 67
  • 9

3 Answers3

4

Alternative via Application.Index() function

For the sake of the art an approach without loops allowing to get any new column order defined just by listing the new column positions via

     Array(1, 4, 2)

in other words

  • the 1st column,
  • (the 3rd one omitted=deleted),
  • the remaining columns 4 and 2 in switched order*.

Btw it would even be possible to repeat columns, just insert its number at any position in the column array, e.g. to repeat a date column with changed formatting (assuming date in column 4 e.g. via Array(1, 4, 4, 2)

Sub DeleteAndSwitch()
'[1]get data
    Dim data: data = Sheet1.Range("A1:D4")
'[2]reorder columns via Array(1, 4, 2), i.e. get 1st column, 4th and 2nd column omitting the 3rd one
'   (evaluation gets all existing rows as vertical 2-dim array)
    data = Application.Index(data, Evaluate("row(1:" & UBound(data) & ")"), Array(1, 4, 2))
'[3]write to any target
    Sheet2.Range("A1").Resize(UBound(data), UBound(data, 2)) = data
End Sub

Related link

See Some peculiarities of the the Application.Index() function

T.M.
  • 9,436
  • 3
  • 33
  • 57
  • 1
    Wonderful. I've seen this in Excel formulas but I have only recently seen this approach in VBA mostly on [VBA for Smarties](https://www.snb-vba.eu/index_en.html) where, among others I found how to create an array of all files in all folders and all sub folders in one(ish) line of code. This is really a treat, one of the top solutions to solve OP's issues.Thanks for sharing. – VBasic2008 Jun 30 '20 at 22:28
  • 1
    Nice! Voted it up. I can slice a 2D array, I can eliminate column, reorder columns, but I cannot insert a column(s) slice in such an array... In fact, I can do it using iteration, but did you find a similar way to **insert** such a vertical slice? – FaneDuru Jul 01 '20 at 07:01
  • Let me think it over later, theoretically there could be a chance - @FaneDuru – T.M. Jul 01 '20 at 08:24
  • Appreciate your perfect interest in getting to the bottom of methods and approaches. - Therefore I tried to give a (partial) answer to your question how to **insert** vertical slices via a separate Q&A [here](https://stackoverflow.com/questions/62722676/insert-vertical-slices-into-array/62722677#62722677) - @FaneDuru – T.M. Jul 03 '20 at 21:11
2

Delete 'n' Switch Column in Array

The code overwrites the third column with data from the second column, and at the same time overwrites the second column with data from the fourth column. Finally the last (fourth) column is removed.

Sub deleteAndSwitch()
    
    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim Data As Variant, i As Long
    Data = wb.Worksheets("Worksheet").Range("A1:D4").Value
    For i = 1 To UBound(Data)
        Data(i, 3) = Data(i, 2)
        Data(i, 2) = Data(i, 4)
    Next i
    ReDim Preserve Data(1 To UBound(Data), 1 To 3)
    'e.g.
    wb.Worksheets("Sheet2").Range("A1") _
      .Resize(UBound(Data), UBound(Data, 2)).Value = Data
 
End Sub
VBasic2008
  • 44,888
  • 5
  • 17
  • 28
  • Thanks for your quick response. Your code works great, is there a way to make it more modular? Basically, the sheet I will be working on will have 30 columns and 500ish rows. I will need to move around many columns and delete about 5-10 columns. Overwriting columns and ReDim the last column out of the array may be a difficult task for a larger sheet. Again, the answer you've given works great, I am just trying to see if there is a way to integrate it into a larger scale. Thanks! – M B Jun 30 '20 at 19:31
  • @M B: I would say that the logic behind the code is clear enough. Enough „modular” if this sounds better... :) It is not something standard. Would you all the time switch the same columns? If not, you must write the code in a way to follow your switching logic/need... And something else: When somebody spend some times and post an answer answering your question, we here tick the code left side check box, in order to make it **accepted answer**. In this way, somebody else searching for something similar will know that the code works... – FaneDuru Jun 30 '20 at 19:37
  • Here is a `Must See` column deleting code by [Tim Williams](https://stackoverflow.com/questions/61308577/array-processing-vba/61309712#61309712) which will shed a light on deleting any column and probably more. But the switching is a little tricky, but you can ask a question for each problem you encounter. Good luck. – VBasic2008 Jun 30 '20 at 19:50
  • @MB: After thinking about your problem I would suggest you watch at least the first of the videos in this [playlist](https://www.youtube.com/playlist?list=PL7ScsebMq5uV493VqnVEIY35SKxUKwWHM). I think this is the most efficient way. – VBasic2008 Jun 30 '20 at 20:48
0

The best way to deal with this issue is to use a better structure than an array. E.g a dictionary of arrays (Collection, Scripting.Dictionary, Arraylist, jagged array). So instead of reading a two dimensional section of a spreadsheet into an array, iterate over the columns and add each column as an array to a dictionary. You can then set up a second simpler dictionary to map each current index to its new index. No need to delete anything as you will only copy back, column array by column array, those columns you actually need.

freeflow
  • 4,129
  • 3
  • 10
  • 18
  • 1
    Can you provide a coded example of your approach? I am not familiar with the VBA dictionary method. Thanks! – M B Jun 30 '20 at 19:58
  • Stackoverflow is not a coding service. You have been pointed to a possible solution. You now have a learning opportunity. I'd suggest starting by looking up VBA collections. – freeflow Jun 30 '20 at 20:37
  • 1
    Why would it be the best way? After processing an array you can simple drop the processing result in a range. So, what arguments do you have when considering such a way being the best? :) – FaneDuru Jul 01 '20 at 07:03