Remove element from array

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
Is there an easy way to eliminate an element from an array that also redimensions the array to exclude the empty element?
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows
You need to move the elements above down (in a loop), and then ReDim Preserve.
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
Office Version
  1. 365
Platform
  1. Windows
Redim Preserve only removes trailing element(s). Otherwise, you can iterate through the array to copy all but that element(s) to another array or copy down as shg said.
 

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
Is there a simple way to empty an entire array? Array() = Empty “Can’t assign to array”<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,782
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Code:
Erase myArray

Read about the Erase statement in Help
 

Glory

Well-known Member
Joined
Mar 16, 2011
Messages
640
This is the code I have now, and it looks like it’s working good.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
<o:p>
Code:
<o:p>    For Each element In r()</o:p>
<o:p>        a = a + 1</o:p>
<o:p>        ReDim Preserve array2(1 To a)</o:p>
<o:p>        If Not IsEmpty(element) Then
            array2(a) = element
        End If</o:p>
<o:p>    Next</o:p>
<o:p>a = Empty</o:p>
<o:p>ReDim r(1 To UBound(array2))</o:p>
<o:p>    For Each element In r()</o:p>
<o:p>        a = a + 1
        r(a) = array2(a)</o:p>
<o:p>    Next</o:p>
<o:p>a = Empty
</o:p>
</o:p>
<o:p></o:p>
<o:p>T</o:p>hanks to both of you
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,108
Office Version
  1. 365
Platform
  1. Windows
You should check that it is working as you think it is. Your counter should go in the If() loop.

Here is my example.
Code:
Option Base 0

Sub Test_Trim1darray()
  Dim a() As Variant
  a = Array("Ken", "", "Hobson")
  MsgBox Join(a, vbLf)
  Trim1dArray a()
  MsgBox Join(a, vbLf)
  
  a = Array("Ken", "", "Hobson")
  MsgBox Join(a, vbLf)
  Trim1dArray a(), "Ken"
  MsgBox Join(a, vbLf)
End Sub

Sub Trim1dArray(ByRef anArray() As Variant, Optional aVal As Variant = Empty)
  Dim a() As Variant, element As Variant, iCount As Long
  iCount = -1
  ReDim a(UBound(anArray))
  For Each element In anArray
    If element <> aVal Then
      iCount = iCount + 1
      a(iCount) = element
    End If
  Next element
  ReDim Preserve a(iCount)
  anArray() = a()
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,051
Members
414,281
Latest member
Engjamal2021

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
Top