Deleting Array Record

M

mchac

Well-known Member
Joined
Apr 15, 2013
Messages
531
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi there,

I'd certainly be using arrays here, although I would use a secondary array as an output, where you go through your initial array and, based on your conditions, either write values to the output array or not, then set the output array to the range (after clearing it's contents of course). Not sure what your conditions are so we can't post any code. If you give more details we can help you out. :)
 
Upvote 0
this works

the select commands are for testing only, not needed to delete row
Code: 
Sub test()
    
    Range("b2:f20").Rows(3).Select  ' this line is not necessary ( test only )
    
    Range("b2:f20").Rows(3).Delete
    
    
    Range("DATA_RNG").Rows(5).Select  ' see that it selects whole row in a range
    
    Range("DATA_RNG").Rows(5).Delete
    


End Sub
 
Upvote 0
thanks jsotola.

Does
Range("DATA_RNG").Rows(5).Delete
delete the row from the range object (in memory) or from the spreadsheet range?

I'll test this in a bit. My initial try with this:

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


    Range("DATA_RNG").Rows(5).Select

resulted in err 1004, Method 'Range' of object '_Global' failed

I'll pull the relevant bits into a short Sub so I can post the code.
 
Upvote 0
In Sheet8 I created a table like this in cells A1:C3:

11 12 13
21 22 23
31 32 33

Then I tried to apply this code to it but get the same err 1004.

Code: 
Sub tester()


    Dim AWBN As String
    Dim DATA_RNG As Range
    
    AWBN = ActiveWorkbook.Name
    
    Set DATA_RNG = Workbooks(AWBN).Sheets("Sheet8").Range("A1:C3")
    
    Range("DATA_RNG").Rows(5).Select


End Sub
 
Upvote 0
While you can delete the rows individually, it kind of defeats going to arrays in the first place, which is efficiency and run time. I'd still stick with the array if I were you. If nothing else, when going through the array for your conditions, putting the row number (index?) of your data into a variable, even a string, then passing that to the range object to delete all at once. Doing this individually will be extremely inefficient. If you post your conditions we can get some efficient code for you. ;)
 
Upvote 0
Thanks Zack. I was just starting to write code to take note of the rows that I want to delete ... or the rows that I don't want to take from my source sheet.

So for example, the first condition that determines that I don't want a row is if cells in columns R:AP are blank.

How do I delete those rows? Everything I've read is that you can't delete the rows of an array.
Perhaps I'm only passing the rows I want from the first array to a second dummy array then redimming the first array and passing the values back?
 
Upvote 0
You can't take out portions of an array, that is correct. Once they are created, that's where they stay, as bits of memory. You can ReDim (re-dimension) an array if you'd like, usually that's to keep a dynamic array. It's hard to give you specifics without knowing how your data is structured or fully what you want to do. If you put your data into an array we'd need to know the range you used so we could correlate an index (e.g. column) for which to look at. Assuming your data was in A1 through AP100, columns R through AP would be columns 18 through 42 respectively. With so many rows I'd still look to use arrays, but you could probably use the WorksheetFunction object here and get a pretty quick calc, or even use Evaluate(), which is probably what I'd do.
 
Upvote 0
Thanks Zack.

I have data in a sheet range A1:AL43124 (sorry, AP was a mistake).

I need to whittle down the number of rows by removing those with no data, those with duplicate data and those that are superseded by more recent data. There may be more rules but those are the starting point.

So I'm just starting to write a routine that will copy the source data and paste it into a second sheet, create a range object from that second sheet, create an array from the range object (maybe the array can be created directly?), create a second array that receives records from the first array that pass the tests, then when the second array is complete paste it back into a range in the second sheet.

Am I heading in the right direction?
I'm happy to provide whatever info you need but I don't want to ask you to do my work for me.


Thanks for your help.
 
Upvote 0
Well, that is a pretty big range and probably won't be a good fit for an array. Unless you only used a single column. There's over a million cells in that range. Here is an example of looping through integers and evaluating if there is anything in columns R through AL of that row, then deleting them when done...

Code: 
Sub foo()

    Dim iStep As Long
    Dim sRows As String
    
    'A1:AL43124
    For iStep = 1 To 43124 'can set dynamically if you need
        If Evaluate("COUNTA(R" & iStep & ":AL" & iStep & ")") = 0 Then
            sRows = sRows & iStep & ":" & iStep & ","
        End If
    Next iStep
    If Len(sRows) > 0 Then
        sRows = Left(sRows, Len(sRows) - 1)
        ActiveSheet.Range(sRows).Delete
    End If
    
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
how to refer to an element in a named range (array/matrix)
Replies
2
Views
188
Pascalvrolijk
P
P
  • Locked
  • Question
VBA: how to refer to an element in a named range (array/matrix)
Replies
1
Views
151
Fluff
Fluff
M
  • Question
VBA to delete the rows which contains the data of previous week!!
Replies
7
Views
231
Alex Blakenburg
A
D
  • Solved
Moving element contents of a 2 dimensional array, either up or down
Replies
4
Views
115
Deutz
D
K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
674
Kakaska
K

Forum statistics

Threads
1,214,374
Messages
6,119,162
Members
448,870
Latest member
max_pedreira

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top