Using CountA and Range

[Davidprince]

I have several lists(50 at last count but possibly 80 - 90) of various sizes(some have 120 items, some have 350 items).
I am using Excel97 to store the lists on a spreadsheet in columns(ie column A is list 1 etc). I set out hereunder my fruitless attempt at solving this problem:

Private Sub CommandButton1_Click()

'assign the numbers to an array

rowcount = Application.CountA(Range("A:A&quot)
ReDim Myarray(rowcount, 1)
For i = 1 To rowcount
Myarray(i, 1) = Worksheets("sheet1&quot.Cells(i, 1).Value
Debug.Print Myarray(i, 1)
Next i

As you can see Myarray is a single column list of varying sizes. This routine copies the list to the array, but I am wondering how I can amend the routine so that "rowcount" counts the items in column B, column C etc preeferably via a loop. PS I tried replacing the one(1) with a j and looped through successfully but couldn't amend the "A:A" to "B:B" or any other letters.
Any suggestions greatly appreciated.
Thanks
David

 

[Loomah]

Hi David
Try this. I haven't looked into what you're doing but there may be a different way to do it - there's (usually) more than one way to skin etc etc. This I think does what you're asking for.

For iCol = 1 To Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    rowcount = Application.CountA(Columns(iCol))
    ReDim Myarray(rowcount, 1)
    For i = 1 To rowcount
    Myarray(i, 1) = Worksheets("sheet1").Cells(i, iCol).Value
    Debug.Print Myarray(i, 1)
    Next i
    Debug.Print "NEXT COL"
Next

Just replace your block of code with this one.
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244