Wrox Programmer Forums
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 23rd, 2007, 09:06 PM
Authorized User
Join Date: Mar 2007
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Array problem

I have a problem of array

I have an excel file and inside the excel file.
I have a set of data and data is filled in cell A10:Z30(some of the cells is empty)

I want to make them to array into another table in to one column but the data need to extract from the original from left to right and up to bottom (A10,B10, C10, D10, ...,A11,B11,C11,....X30, Y30, Z30)
If the cell is empty then skip it.

Please help me. Thanks

Old July 24th, 2007, 01:15 AM
Friend of Wrox
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post

Hi Ivan,

Heres a function that I have just whipped up which should solve your problem.
Obviously if you need to cusomise it, then feel free to do so.

It will return an Array from a Range where any cells are not blank.
Public Function NonBlankCellValuesFromRange() As String()
On Error GoTo NonBlankCellValuesFromRange_Err

    'Create Reference to the Working Sheet, for this example
    'use the Active Sheet.
    Dim ws As Worksheet
    Set ws = ActiveSheet

    'First Define the Range to Work with
    'Use the Range posted (A10:Z30)
    Dim r As Range
    Set r = ws.Range("A10:Z30")

    'Create an Array to hold our values.
    Dim arr() As String
    Dim arrSize As Long
    arrSize = 0

    'Need to loop through each column within each row,
    'adding to an Array in NOT empty.
    Dim row As Range, col As Range

    For Each row In r.Rows
        For Each col In row.Columns

            'If the Value is not Blank, than add to the Array.
            If Not (col.Value = vbNullString) Then
                ReDim Preserve arr(arrSize)
                arr(arrSize) = col.Value
                arrSize = arrSize + 1
            End If


    'Return the Array
    NonBlankCellValuesFromRange = arr

    'Clean Up
    Set r = Nothing

    Exit Function

    Debug.Print "Error!" & vbCrLf & "Number: " & Err.Number & vbCrLf & Err.Description
End Function
I hope this helps.


<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".

Thomas Jefferson</center>
Old July 24th, 2007, 08:28 PM
Authorized User
Join Date: Mar 2007
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for your support.

As I am not familiar with vba, I failed to get the reuslt that i want after running your vba, could you please explain more to me. Thanks

Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Problem...Can anyone help?? :( Shuchik Classic ASP Basics 1 September 25th, 2007 05:21 AM
problem in getting the value of an array knightneo Beginning PHP 0 December 4th, 2006 10:23 PM
Array Problem monika.vasvani ASP.NET 1.0 and 1.1 Professional 3 September 13th, 2006 05:56 AM
Array problem NEO1976 Javascript 0 August 3rd, 2006 03:55 AM
Array Problem hayley Classic ASP Basics 4 February 2nd, 2005 12:42 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.