Deleting Elements in an Array if Element is a Certain value VBA

by

An array is a structure with a certain size. You can use dynamic arrays in vba that you can shrink or grow using ReDim but you can’t remove elements in the middle. It’s not clear from your sample how your array functionally works or how you determine the index position (eachHdr) but you basically have 3 options

(A) Write a custom ‘delete’ function for your array like (untested)

Public Sub DeleteElementAt(Byval index As Integer, Byref prLst as Variant)
       Dim i As Integer

        ' Move all element back one position
        For i = index + 1 To UBound(prLst)
            prLst(i - 1) = prLst(i)
        Next

        ' Shrink the array by one, removing the last one
        ReDim Preserve prLst(Len(prLst) - 1)
End Sub

(B) Simply set a ‘dummy’ value as the value instead of actually deleting the element 

If prLst(eachHdr) = "0" Then        
   prLst(eachHdr) = "n/a"
End If

(C) Stop using an array and change it into a VBA.Collection. A collection is a (unique)key/value pair structure where you can freely add or delete elements from 

Dim prLst As New Collection

More Related Contents:

  1. Excel-VBA – Insert new first column in datafield array without loops or API calls
  2. Creating an Array from a Range in VBA
  3. Expanding column cells for each column cell
  4. How do I slice an array in Excel VBA?
  5. Check if a value is in an array or not with Excel VBA
  6. Matching values in string array
  7. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  8. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  9. Return Index of an Element in an Array Excel VBA
  10. Writing an array to a range. Only getting first value of array
  11. Excel VBA: Range to String Array in 1 step
  12. Faster way of using Sumifs()
  13. How to initialize a multidimensional array variable in vba for excel
  14. VBA using ubound on a multidimensional array
  15. Turn Excel range into VBA string
  16. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  17. Why am I having issues assigning a Range to an Array of Variants
  18. Subscript out of range error with an array – no idea why?
  19. VBA Excel large data manipulation taking forever
  20. Performance difference between looping range vs looping array
  21. Create Chart from Array data and not range
  22. Excel VBA Quickest way to sort an array of numbers in descending order?
  23. Simple VBA array join not working
  24. Excel VBA – How to Redim a 2D array?
  25. ReDim Preserve “Subscript Out of Range”
  26. how to combine duplicate rows and sum the values 3 column in excel
  27. Populating VBA dynamic arrays
  28. Dynamically Dimensioning A VBA Array?
  29. VBA – Returning array from Property Get
  30. How do I Merge two Arrays in VBA?

Leave a Comment