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.

 

[Zack Barresse]

Oh, I didn't realize removing duplicates was a previous step of the process, I thought it was a means to an end. In that case, yeah, using the built-in functionality would be best. So for #1, you are absolutely right. For #2 & 3 I would use a helper column to evaluate your conditions, then delete the rows matching. But you'd need to define the logic for #3. You did for #2, which if I understand correctly, is blank cells from columns R through AL - if all those cells of the row are blank, that row gets deleted. We do need to know if there are formulas returning null values (not a true blank) or actual blank cells.

 

[Rick Rothstein]

A couple of comments...

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 can eliminate the error trap and call-out to the worksheet ADDRESS function by doing it this way...

Function COLUMNLETTER(ByVal ColumnNumber As Long) As String
  If ColumnNumber <= Columns.Count Then COLUMNLETTER = Split(Cells(1, ColumnNumber).Address, "$")(1)
End Function

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

With 43000 rows of data, it is possible for SpecialCells to fail in versions of Excel prior to XL2010 if the number of non-contiguous areas of blanks exceeds the limit of 8192 in those versions. A technique like described in my mini-blog article here can be used to overcome the problem...

Overcoming Limitations in the VBA SpecialCells Function

 

[Zack Barresse]

Hi Rick!

Yes, you could get rid of the erroneous error trapping, but you can't use your method unless there is an active sheet, which I generally try to avoid. I don't think the error handling was actually needed, but I did it out of habit.

For SpecialCells, you make a good point. I was thinking about using a helper column to avoid that method.

 

[Rick Rothstein]

Yes, you could get rid of the erroneous error trapping, but you can't use your method unless there is an active sheet, which I generally try to avoid. I don't think the error handling was actually needed, but I did it out of habit.

I am guessing you mean my code won't work if, say, a Chart sheet is active instead of a worksheet. If so, I am pretty sure your code will fail for that situation as well. As for the error checking... it is needed to protect against a numeric argument that exceeds the number of columns on a worksheet (for example, 20000); hence, the If..Then test I used in my code.

 

[mchac]

Zack, my steps 1 and 2 are interchangeable but the result of those two is a unique set of SEC filing documents (each on a row) with some select data from them for a given corporate. In step 3, if there are multiple docs for a given corp I sort through them to find the one that is most suitable for me.

No formulas in the tested columns. Just data or blank.

Update regarding the .RemoveDuplicates. I had Option Base 1 at the top of the module and removing it allows it to work. I have no idea why this is the case. The range is A2:A43124 and the object DATA_RNG is 1 to 43123. Though now I need to go back and check my array indexing.

Rick, thanks for your input. I'm on Excel 2010 and there aren't 8000 blank cells but I'm interested to read your article.

 

[Zack Barresse]

@Rick: I mean no active sheet. Say, if no workbook is open, there is no active workbook or active sheet. It will error out on a chart sheet though, yeah. And probably a Dialog sheet as well. I don't mind that type of error handling in small functions like this, it's not that expensive, albeit slightly lazy.

@mchac: Rick's article is a definite read. Good stuff there. Can you tell us what your conditions for #3 is?

 

[Rick Rothstein]

@Rick: I mean no active sheet. Say, if no workbook is open, there is no active workbook or active sheet. It will error out on a chart sheet though, yeah. And probably a Dialog sheet as well. I don't mind that type of error handling in small functions like this, it's not that expensive, albeit slightly lazy.

I didn't get a lot of sleep last night, so maybe I am just too tired to think straight, but under what conditions are you thinking about where a function can be executing without a workbook open or a sheet active?

 

[Zack Barresse]

I keep most of my functions in my Personal.xlsb file, or with an add-in, and that's always good to check if your routine will be stored there.

As far as checking if it's a worksheet, you could use a one-liner for that... 'If ActiveSheet.Type <> xlWorksheet ...' But that assumes there's an active sheet as well. I know you don't need the error handling, just me being lazy lol.