View Full Version : [SOLVED:] VBA Array Function - Return Array from Range without Blanks
Hiersch
06-13-2016, 02:00 AM
Dear all,
I am struggling with a basic problem in VBA and would appreciate some help. I want to define a function which returns an array from a range without blanks, as shown in the attached file.
This is the code I have so far:
Function portfolioX(N)
Dim MyArray(3)
Dim i AsInteger
counter =1
For i =1To N
If IsEmpty(i)Then
Next i
Else
portfolio = MyArray
MyArray (counter)
counter = counter +1
Next i
EndIf
EndFunction
I am a newbie to VBA, so this could be completely wrong. Thanks! I am attaching the workbook to this thread.
16366
mancubus
06-13-2016, 02:07 AM
welcome to vbax.
your workbook contains a table.
eloborate your requirement.
which cells will be in the array.
will it be a 1D or 2D array?
Hiersch
06-13-2016, 02:21 AM
Hey mancubus,
Thank you for your quick reply. The array will be in grey cells O2:R30. Basically, when I run the macro, all grey cells should populate with security names as displayed in the green cells on the left. In cells S2:V2, a sample line is displayed what the VBA should return in row 2 (O2:R2). Hope this is clear. Thank you
mancubus
06-13-2016, 02:46 AM
i wouldn't use a udf for this.
try below macro
Sub vbax_56315_NonContiguousRange_to_ContiguousRange()
Dim i As Long, j As Long, k As Long
For i = 2 To 30 'from row 2 to 30
k = 15 'reset to col O at each row change
For j = 2 To 14 'from col B to N
If Cells(i, j) <> "" Then
Cells(i, k) = Cells(i, j)
k = k + 1
End If
Next j
Next i
End Sub
Sub M_snb()
y = 15
For Each it In Cells(2, 2).Resize(, 13).SpecialCells(2)
Cells(2, y) = it
y = y + 1
Next
End Sub
Hiersch
06-13-2016, 03:51 AM
Dear all,
Thanks for your contributions. This post has solved the issue: I cannot post links yet, maybe someone can do this.
Google: "Pearson Software Consulting Eliminating Blank Cells In A Range". It is the first hit.
mancubus
06-13-2016, 04:15 AM
http://www.cpearson.com/excel/NoBlanks.aspx
mancubus
06-13-2016, 04:17 AM
you can paste links when your post count is 5 or more.
but you can mark your threads as solved (from Thread Tools dropdown) anytime.
Hiersch
06-13-2016, 04:26 AM
Already done. Thank you!
mancubus
06-13-2016, 05:30 AM
??
Hiersch
06-13-2016, 06:07 AM
I meant I marked the thread as solved
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.