0

Suppose you have an array of numbers, and they are the column numbers that you would like to delete. A smart idea is to convert them to letters and then concatenate them and delete all the columns, as shown here by @Siddharth Rout. But there is a problem, it seems there is an upper limit of string inside range, so say

str = "AB:AB,CJ:CJ,CZ:CZ,NJ:NJ,NK:NK,NL:NL...",
Len(str)=300, 'Just about 50 columns, not too many indeed, there are 16384 columns in Excel 2010!!!

Chances are you will get an error if you use Range(str).Delete Shift:=xlToLeft, how to solve this problem?

Nicholas
  • 2,560
  • 2
  • 31
  • 58
  • 1
    If you're running into a character limit for your string, would it work if instead you just did `str = "AB, CJ, CZ, NJ, ..."` and then maybe store those in an array, i.e. `colArray = Split(str, ",")`and iterate through that? Another option would be that if there's some sort of pattern to the columns you want to delete, write a loop that uses that pattern? – BruceWayne Jun 18 '17 at 20:42
  • 1
    I would use column numbers, and the `Union` operator to set up your non-contiguous range of columns. Depending on the number of columns, you may have to do this in stages. In a test, I combined 667 non-contiguous columns into a single range, and then deleted those columns. – Ron Rosenfeld Jun 18 '17 at 21:09
  • Note that the answer posted by Siddharth Rout (that you link to) shows the way to use `Union` to get around the issue you are having. – YowE3K Jun 18 '17 at 21:28
  • @YowE3K For the `Union` approach, do I need to use `eval`? Just wonder how do I concatenate them based on an array of numbers? – Nicholas Jun 18 '17 at 21:54
  • I'm not sure what the "array of numbers" is that you are referring to, but you can do it as Siddharth showed in the answer you linked to (e.g. `Union(.Columns(28), .Columns(88), .Columns(104), .Columns(374), .Columns(375), .Columns(376), ....)`) or you could use column letters (e.g. `Union(.Columns("AB"), .Columns("CJ"), .Columns("CZ"), .Columns("NJ:NL"), ....)`) or you could do it in a loop (e.g. as done in [one of the answers](https://stackoverflow.com/a/6001010/6535336) to the question I suggested this was a dup of). There is no need for an `Evaluate`. – YowE3K Jun 18 '17 at 22:11
  • @YowE3K I mean something like arr = split("28-88-104","-"), how do you use them in `Union(.Columns(28), .Columns(88), .Columns(104)`? – Nicholas Jun 18 '17 at 22:24
  • Look at the answer in the dup, then create your loop through all your positions of `arr` and use something like `.Columns(CLng(arr(i)))` instead of that answer's `Sheets("Data").Range("A" & i)`. P.S. How are you creating the string `"28-88-104"`? Instead of creating that string, why don't you create the `Union` at that point instead of creating the string and then pulling it apart again? – YowE3K Jun 18 '17 at 22:44
  • @YowE3K Good question. The reason is I can't find a data structure in VBA like list in Python that I can append number one by one into an array. But it's easier to use str = str & "-2" and then split the str into array. I got these numbers one by one rather than in a batch. Any idea here? – Nicholas Jun 18 '17 at 23:07
  • 1
    I would recommend that, instead of appending the column numbers into a string, you `Union` each `Column` into a `Range`. If you include the part of your code that is creating that string into the question, it sounds like it would be easy to modify to do the `Union`. – YowE3K Jun 18 '17 at 23:41

1 Answers1

1

Another option

Option Explicit

Public Sub DeleteColumns()
    Dim i As Long, arr As Variant

    arr = Split("3-5-7", "-") 'ascending order

    Application.ScreenUpdating = False
    With Sheet1
        For i = UBound(arr) + 1 To LBound(arr) + 1 Step -1
            .Cells(Val(arr(i - 1))).EntireColumn.Delete
        Next
    End With
    Application.ScreenUpdating = True
End Sub

(slow for a large number of columns)

paul bica
  • 10,557
  • 4
  • 23
  • 42
  • slow for a large number of columns? Is it due to the `for` loop (rather than delete them all together) or what? Where comes this statement? – Nicholas Jun 18 '17 at 23:09
  • yes, deleting the column one by one is slow (see [this question](https://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less) related to deletions). the other option is to split a string like "A:A, C:C, X:Z..." into smaller strings of 250 chars or less, delete the group of columns, then move to the next group – paul bica Jun 18 '17 at 23:13
  • or you could hide all columns to delete and copy all visible data (columns) to a new sheet (like in my answer in the link) – paul bica Jun 18 '17 at 23:16
  • Really appreciated Paul! – Nicholas Jun 18 '17 at 23:19