Assign column to an array VBA

Question

I have this code. DataSet is set as a variant.

DataSet = Selection.Value

Works fine but is there a way I can change it to just column A, specifically cells A2 to A502? Ive tried setting that as the range but it doesn't work. It also needs to ignore blank spaces because not all of the cells will have content. I am trying to eliminate the need to highlight the cells as the entries will only be in that specific range.

To ignore the blanks you will need to iterate through the range one by one and add to the array if not blank. To do it all in one Dataset = Range("A2:A502").Value — Scott Craner
– Scott Craner, Commented Jun 12, 2017 at 18:48

paul bica · Accepted Answer · 2017-06-13 04:05:53Z

Try these 2 versions:

Option Explicit

Public Sub getNonemptyCol_ForLoop()
    Dim dataSet As Variant, fullCol As Variant, i As Long, j As Long
    Dim lrFull As Long, lrData As Long, colRng As Range

    Set colRng = ThisWorkbook.Worksheets(1).Range("A2:A502")
    fullCol = colRng
    lrFull = UBound(fullCol)
    lrData = lrFull - colRng.SpecialCells(xlCellTypeBlanks).Count
    ReDim dataSet(1 To lrData, 1 To 1)

    j = 1
    For i = 1 To lrFull
        If Len(fullCol(i, 1)) > 0 Then
            dataSet(j, 1) = fullCol(i, 1)
            j = j + 1
        End If
    Next
End Sub

Public Sub getNonemptyCol_CopyPaste()   'without using a For loop
    Dim dataSet As Variant, ws As Worksheet

    Application.ScreenUpdating = False
    Set ws = ThisWorkbook.Worksheets(1)
    With ws.UsedRange
        ws.Activate
        .Range("A2:A502").SpecialCells(xlCellTypeConstants).Copy
        .Cells(1, (.Columns.Count + 1)).Activate
        ActiveSheet.Paste

        dataSet = ws.Columns(.Columns.Count + 1).SpecialCells(xlCellTypeConstants)
        'dataSet now contains all non-blank values

        ws.Columns(.Columns.Count + 1).EntireColumn.Delete
        .Cells(1, 1).Activate
    End With
    Application.ScreenUpdating = True
End Sub

Dimi · Accepted Answer · 2019-03-28 11:59:20Z

1

Assign with dynamic column.

Sub SetActiveColunmInArray()
    Dim w As Worksheet
    Dim vArray As Variant
    Dim uCol As Long
    Dim address As String


    Set w = Plan1 'or Sheets("Plan1") or Sheets("your plan name")
    w.Select
    uCol = w.UsedRange.Columns.Count
    address = w.Range(Cells(1, 1), Cells(1, uCol)).Cells.address

    vArray = Range(address).Value2

End Sub

edited Mar 28, 2019 at 11:59

answered Mar 26, 2019 at 19:11

Dimi

362 bronze badges

1 Comment

Bob Dalgleish Over a year ago

Rather than just providing the code, it will help the original poster any one else who finds this question to give some narrative description of what you are doing.

Collectives™ on Stack Overflow

Assign column to an array VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related