Delete one ore more columns within an array in VBA

[atf32]

Is it possible to pull in a table of data into an array and then delete a specific column of data? Perhaps one or more, if I determine that I only want certain columns of data? Looking for VBA code to perform this.

 

[mart37]

You can make a loop with all rows (x)
you can make a loop with all columns started from the column you want to delete minus the last (Y)
move to left: table(x,y) = table(x,y+1)
delete the last column

 

[atf32]

Do you have a simple example to show?

 

[Eric W]

Reading a table of data into an array is good, it speeds up processing. But once it's in the array, why do you want to delete certain columns? If it's for internal processing within your VBA program, just leave them alone. As mart37 said, you can write a simple loop to move data from one column to the previous column, but this takes up processing time for no real benefit.

The other reason I can think of to do this would be to prepare the array to write it back out to a sheet. If that's the case, you can write back the whole array, then delete the columns you don't want. There are also ways to selectively write out the columns you want.

If you have a different reason to delete columns within an array, let us know.

 

[mart37]

Dim myArray As Variant
Dim lr As Long 'last row
Dim lc As Long 'last column
Dim dc As Long 'delete column
dc = 3
myArray = Cells(1).CurrentRegion
lr = UBound(myArray, 1)
lc = UBound(myArray, 2)
For x = 1 To lr
    For y = dc To lc - 1
        myArray(x, y) = myArray(x, y + 1)
        If y = lc - 1 Then myArray(x, y + 1) = ""
    Next y
Next x

 

[Peter_SSs]

Is it possible to pull in a table of data into an array and then delete a specific column of data? Perhaps one or more, if I determine that I only want certain columns of data? Looking for VBA code to perform this.

If I have understood correctly, I think you can achieve that without any looping at all. In addition, you can swap the order of columns around if you want.

Here is an example that reads data from rows 2:20 of a worksheet into an array. It only reads the columns we are interested in, and does so in the order we want. In my case, columns D, E, H and A in that order. That is, it omits columns A:C and F:G.

If you are talking about removing columns from an array already in memory, then my code does that by only extracting columns 4 and 2 (again in that order) from the original array. This produces an array that contains just 2 'columns' and those 2 columns would contain what was originally in columns A and E in the worksheet.

Hope that made sense.

Sub SomeColumnsOnly()
  Dim sCols As String
  Dim aRws As Variant, aCols As Variant, DataArray As Variant
  
  sCols = "4 5 8 1" '<- Columns of interest: D, E, H and A in that order
  aCols = Split(sCols)
  aRws = Evaluate("Row(2:20)") '<- This example is for rows 2:20
  
  'This reads columns D, E, H and A into an array
  DataArray = Application.Index(Columns("A:H"), aRws, aCols)
  
  'This extracts columns 4 and 2 only, and in that order, from the above array
  'That is, it will contain data from columns A & E of the worksheet
  aRws = Evaluate("Row(1:" & UBound(DataArray) & ")") '<- Now we are interested in the 'rows' of DataArray
  DataArray = Application.Index(DataArray, aRws, Array(4, 2))
  
End Sub