PDA

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

snb
06-13-2016, 02:46 AM
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