CODEWITHSUNDEEP
arraysexcelvbarowmissing-data
Maeaex1
Maeaex1

Reputation: 755

VBA: remove rows from array that have more than X empty values

I have a dynamic array(n,m) that gets values assigned from a range.

As the range contains entries that do not have all necessary values (if complete entry then 5 or 6 values per row). Now I want to delete the entire rows of the array that only contain less than 3 values.

The Array in this example has the shape (60,4).

Array

Thats how I assign the values to the array:

Dim CFarr() As Variant
CFarr() = ws_src_CF.Range(ws_src_CF.Cells(ref2, 1), ws_src_CF.Cells(EoCF, lastC)).Value

Thanks for your help!

Upvotes: 0

Views: 1927

Answers (1)

MKaras
MKaras

Reputation: 727

The problem is you can't really remove a row from an array. Instead of removing the elements you don't want, you have to select the elements you want, copy them to a new array and use that new array instead of the old one.

You have 2 options:

1) Loop through rows of array, count blank elements in each row and either copy it to a new array or not.

2) In your worksheet add a column "Delete" which will (based on formulas for example) tell if the row should be deleted or not, then filter the sheet based on this column and copy the range to array. This way you in the beginning you will take only the elements you want, so you won't have to delete anything. You can remove the filter and added column afterwards if you don't want to change the sheet.

Upvotes: 2

Related Questions