Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

removing empty data points

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Removing empty data points in an array

Removing empty data points in an array

ResolvedVersion 2010

Removing empty data points in an array

Hi Guys,

I've created some code that stores an array of product info and a user form that enables the user to select certain product properties to search for within the array.

Once the products the user is searching for are found within the original array (using a series of if statements) the info of that product is copied into a new array called "search results".

I want to display the results to the user, but currently the new array is the same size as the original, and the products found in the search are in the same locations as they were in the original. I've added some more code to reposition the products and then remove the empty array points however it doesn't seem to work. It's not recognising that there is any data in the points in the array. See the code below:

'Below trying to remove empty array locations
Dim NewSearchRe As Variant

ReDim NewSearchRe(1 To UBound(SearchResults, 1), 1 To UBound(SearchResults, 2))

'The below repositions the search results in the array to the first few positions
For c = 1 To UBound(NewSearchRe, 2)
For r = 1 To UBound(NewSearchRe, 1)
If SearchResults(r, c) <> "" Then
i = i + 1
NewSearchRe(i, c) = SearchResults(r, c)
End If
Next r
Next c

'the below transposes the array so that the second array dimension can be resized and then transposes back

With WorksheetFunction
SearchResults = .Transpose(NewSearchRe)
ReDim Preserve NewSearchRe(LBound(NewSearchRe, 1) To UBound(NewSearchRe, 1), LBound(NewSearchRe, 2) To i)
NewSearchRe = .Transpose(NewSearchRe)
End With


Are there any alternatives to the above code that you know of??

RE: Removing empty data points in an array

Hi Chris,

Thank you for the forum question.

We can use the isempty function to test if we have items in an array.

Please have a look at my example below. I am not using For Next loops but the for each loop. I have tested the code with some 1 dimension arrays and it is working fine.

I hope this can guide you in the right direction.

Sub tetett()


Dim NewSearchResults() As Variant
Dim SearchResults(1 To 2) As Variant

SearchResults(1) = "lemon"
SearchResults(2) = "apple"


ReDim NewSearchResults(1 To UBound(SearchResults, 1))

'The below repositions the search results in the array to the first few positions

For Each Item In SearchResults
If Not IsEmpty(Item) Then


NewSearchResults = SearchResults
End If

Next


End Sub


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Removing empty data points in an array

Thanks Jens, very helpful.

Don't suppose you know how I can register the "Microsoft Slider Control, Version 6" active X control?

Whenever I try to use it on my laptop it says the library isn't registered.

Confused.com

RE: Removing empty data points in an array

Hi Chris,

I am sorry about the late reply to the question.

Please have a look at the link below, It will show you have to register activeX controls.


https://support.office.com/en-za/article/Add-or-register-an-ActiveX-control-8fc743a1-e72b-4b55-af3a-85d326e53918 #bm2


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 4 Feb 2015: Automatically marked as resolved.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Removing the Ribbon from view in Excel 2010

At times when you want to view the whole spreadsheet, try double clicking on the ''Home'' tab on the ribbon which will hide the ribbon from view.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.