1

I have a code that goes through the "hello" sheet and deletes all of the columns that contain the word B at the top of the column:

let ws = workbook.getWorksheet("hello");
let usedrange = ws.getUsedRange(true);    
let qtycol = usedrange.getColumnCount() - 1;
let values = usedrange.getValues();

for (let j = qtycol; j >= 0; j--) {

    let command = values[1][j];

    if (command == "B") {
        usedrange.getColumn(j).delete(ExcelScript.DeleteShiftDirection.left);
    }
}

When the file was smaller, there were no issues. But now that the file is a bit larger, I get teh following error:

Line 64: Range getColumn: Timeout

Line 64 is the line that starts with usedrange...

Is there a more efficient way of doing this? Would love to get feedback or other ideas.

Thank you!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
aniskhan
  • 11
  • 1
  • Just one question: is the `values` array 0 or 1 based? You've got a 1 for the first row index, but your loop goes down to 0 for the column. Is that causing any problems? – Christopher Hamkins Jan 05 '22 at 14:35

1 Answers1

0

I suspect that when a lot of columns are being deleted the updating of the worksheet may be causing your problem.

To get around this, you could build up a multiple-area range (that is a read-only operation that won't modify the worksheet) and then delete it in a single operation.

I don't have access to ExcelScript, but this is the equivalent VBA routine that would do it:

Public Sub bla()

    Dim J As Integer
    Dim Command As String
    Dim qtycol As Integer
    Dim ws As Worksheet
    Dim UsedRange As Range
    Dim toDeleteRange As Range
    
    Set ws = Application.ActiveWorkbook.Worksheets("hello")
    
    Set UsedRange = ws.UsedRange
    Let qtycol = UsedRange.Columns.Count - 1
    
    For J = qtycol To 1 Step -1 '(let j = qtycol; j >= 0; j--) {
    
        Let Command = UsedRange(1, J)
    
        If (Command = "B") Then
            If (toDeleteRange Is Nothing) Then
                Set toDeleteRange = UsedRange.Columns(J)
            Else
                Set toDeleteRange = Union(toDeleteRange, UsedRange.Columns(J))
            End If
        End If
    Next J
    
    toDeleteRange.Delete (XlDeleteShiftDirection.xlShiftToLeft)

End Sub

You could use the analogous routines from ExcelScript.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Christopher Hamkins
  • 1,442
  • 9
  • 18
  • Hi Christopher, thank you for the idea. Unfortunantely, I can´t get it to work on ExcelScript (I am new to the language)... Could someone please see if this is possible and how would you go about doing it? – aniskhan Jan 13 '22 at 17:58
  • Note that columns are counted in `Long` not `Integer` (you got unnecessary implicit type conversions going on there). Also there is almost no reason to use `Integer` in VBA [you can almost always use `Long` instead](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520). – Pᴇʜ Jan 18 '22 at 07:12
  • @aniskhan This is VBA not ExcelScript. If you are using 3ʳᵈ party tools (ExcelScript) with Excel you should better give a link to those tools to make it easier to give answers. This answer is written in VBA and needs to be included in Excel itself. – Pᴇʜ Jan 18 '22 at 07:14