Deleting Array Record

[mchac]

There are lots of hits when searching this topic but I've not found anything specific to my question which likely means it can't be done, but here goes:

I have a range of cells in Excel that I set to an a range object cunningly named DATA_RNG.
I need to evaluate that range object and delete some of the rows but since it has 43k rows x 30 columns I don't want to do that element by element. So I create an array by:
DATA_ARRAY = DATA_RNG

Let's say I want to delete record (row) 5 of DATA_ARRAY.

I understand that there is no ARRAY.EntireRow.Delete object. Rather than move one element at a time then back again I'm wondering if I can do something like the following:

dim DATA_ARRAY_DUMMY()
DATA_ARRAY_DUMMY(1 to 4, 1 to 30) = DATA_ARRAY(1 to 4, 1 to 30)
DATA_ARRAY_DUMMY(6 to 43000, 1 to 30) = DATA_ARRAY(6 to 43000, 1 to 30)


Any thoughts are appreciated.

 

[mchac]

it's a 38 col array so you think it's better to evaluate the excel sheet itself rather than load the data into an array and work on it there?

 

[Zack Barresse]

At 1.6 million cells, yeah, I wouldn't use an array. But nor would I loop through a range object either. The sample I posted loops through integers and uses the Evaluate() function, much more robust than working with the Range object.

 

[mchac]

ok. thanks for your help.
now i'm curious about it. since during this board exchange i was writing some code to use an array. i'll finish that then write something like what you suggested and compare processing time.

regarding the Range Object, does Excel load the range into memory bypassing an excel interface, like it does for arrays, or is the Range Object just a pointer to the excel sheet so working with it is the same as working with a sheet directly?

 

[Zack Barresse]

Well, both really. It loads the object by reference to the cells on the sheet you're pointing to, which thus loads that variables data/properties into memory. Unlike an array which doesn't have a parent object, a Range is an actual object, something 'physical'. It has methods and properties, unlike an array which doesn't. Reading/writing to arrays is always faster than reading/writing to a range object.

 

[mchac]

I have some code that appears to work, though I'm sure it's not the most efficient. Happy to post if you'd like to see it.

Also, I'm trying to remove one of the parts of the code that eliminates duplicates in an array by copying only the records i want into a second array. Instead I'm trying to perform this task first by using .RemoveDuplicates on the Range Object but I'm getting subscript out of range. I pulled the relevant code out and have this which gives the same error at the .RemoveDuplicates line. The Set DATA_RNG line works fine.

Sub tester()

    Dim AWBN As String
    Dim ASN_2 As String
    Dim DATA_RNG As Range
    Dim LAST_ROW_NUMBER As String
    Dim LAST_COLUMN_NUMBER As Integer
    Dim LAST_COLUMN_LETTER As String

    AWBN = ActiveWorkbook.Name
    ASN_2 = ActiveSheet.Name
    
    LAST_COLUMN_NUMBER = Sheets(ASN_2).Cells.Find("*", Sheets(ASN_2).Range("A1"), xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    LAST_COLUMN_LETTER = ColumnLetter(LAST_COLUMN_NUMBER)
    
    LAST_ROW_NUMBER = Sheets(ASN_2).Cells.Find("*", Sheets(ASN_2).Range("A1"), xlFormulas, xlPart, xlByRows, xlPrevious).Row

    Set DATA_RNG = Workbooks(AWBN).Sheets(ASN_2).Range("A2:" & LAST_COLUMN_LETTER & LAST_ROW_NUMBER)

    DATA_RNG.RemoveDuplicates Columns:=Array(2, 3, 5)

End Sub

The DATA_RNG object has dimensions (1 to 43123, 1 to 38) and the data looks ok.

If you see any problems with this I'd really appreciate a post.

Thanks again for your help so far.

 

[Zack Barresse]

For uniques it's probably easier to use a Collection or Dictionary, which can only accept unique values and you test with values going in to it.

In your above code, where you set the LAST_COLUMN_NUMBER, I would change the 'xlFormulas' to 'xlValues'. A value below a cell with a formula won't be picked up, but a formula returning any value (even a null string) will be picked up with values.

I don't know what the ColumnLetter() function is. It obviously gets the letter from a column number, but I can't speak to it's efficiency.

Overall I think you'd be better served with a loop like I posted earlier. You can substitute the upper bound of the loop with a dynamic row and/or column. Did that code not work for you?

 

[mchac]

Sorry, here's the ColumnLetter Function

Function ColumnLetter(ColumnNumber As Integer) As String
    Dim n As Integer
    Dim c As Byte
    Dim s As String


    n = ColumnNumber
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    ColumnLetter = s
End Function

It works and it's only called once or twice so a faster method won't be noticeable I don't think.

I didn't try your code since understand native Excel functions are much faster (am I wrong about that?). Hence I tried to get .RemoveDuplicates to work.

 

[jsotola]

this code deletes all rows in range A1:AD43000 that have a blank cell in column A

does not delete any data outside of that range

Sub Macro1()
    
    Dim rng As Range
    Dim uni As Range
    Dim i As Integer
    
    
    Set rng = Range("A1:A43000").SpecialCells(xlCellTypeBlanks)
    Set uni = rng
    
    For i = 1 To 29        ' extend range to 30 columns
        Set uni = Union(uni, rng.Offset(, i))
    Next i
    
'    uni.Select          ' this command is just for visually checking the correct range
'    Range("a1").Select  '
    
    uni.Delete xlUp
    
End Sub

 

[Zack Barresse]

For the column letter, you don't need a loop...

Function COLUMNLETTER(ByVal ColumnNumber As Long) As String
    On Error Resume Next
    COLUMNLETTER = Split(Evaluate("ADDRESS(1," & ColumnNumber & ")"), "$")(1)
    On Error GoTo 0
End Function

You don't even need an activesheet for this to return an address. The only downside is it won't work in Excel 97 and before because there is no Split() function for that. There is a workaround if it's needed though.

As for deleting all blank rows with blanks in column A, again, you can shorten that and lose the loop...

    On Error Resume Next
    Range("A1:A43000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0

It's generally a good idea to use built-in functionality when you can, yes, but you're wanting more conditions after that, and removing duplicates is just the first step. Or have I got that wrong? Anyway, the point I was trying to make was even though built-in functionality is a good idea, the overall effort should be weighed as well.

 

[mchac]

Thanks jsotola.

Zack, thanks for the Split(Evaluate(Address code. I think that's slick. (I'm in Excel 2010.)

Regarding Range("A1:A43000").SpecialCells(xlCellTypeBlanks).EntireRow.Delete, the test I have is if the last 21 of the 38 columns I have is blank for any row, I delete that row. So would I set R2:AL43124?
Also, does it work for columns that are not next to each other?

There are at least three tasks:
1. remove dupes
2. remove records that have blanks in specific sets of cells.
3. remove records that don't have a specific flag in a cell.

It seemed like (1) was best handled via native Excel functions since they are compiled.
Then it seemed like (2) and (3) required going back and forth between VBA and Excel each time a row is evaluated, and my understanding is that this is expensive, so I thought reading into an array, doing the evaluation in the array then setting the resulting array equal to a range would be fastest. Maybe the specialcells functionality is superior. I'll test it against what I have.

My biggest problem currently is solving the Subscript out of range error when using .RemoveDuplicates. No idea how to track that down. Any thoughts are appreciated.