Store values from two sheets into an array

[YasserKhalil]

Hello everyone
I have sheet1 and sheet2 and I need to store values from Sheet1.range("A2:A" & LastRow) .. LastRow here is 15 and at the same time add the values of Sheet2.range("A2:A" & LastRow) .. LastRow here is 20 ...
I can store only values from one sheet like that

myData = Sheet1.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value

How to add Sheet2.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value) to form one array for the values from the two sheets?

 

[Hui]

like :
Dim newRng as Range

Set rng1 =Sheet1.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
Set rng2 =Sheet2.Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value)

newRng = Union(rng1, rng2)

 

[YasserKhalil]

Thanks Mr. Hui
First I don't need to declare these as ranges .. I need to deal with the range values not the range itself
Second I got error when trying to union the two ranges ..It seems that Union doesn't work for two sheets ..
Third as for .Value at the end of assigning range variable gives errors ..

 

[Hui]

Can you post a sample file please

 

[jindon]

Sub test()
    Dim a, x As Long, y As Long
    x = Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row
    y = Sheets("sheet2").Range("a" & Rows.Count).End(xlUp).Row
    Sheets("sheet2").Rows("1:" & x).Insert
    a = Evaluate("if(row(1:" & x + y & ")<=" & x & ",sheet1!a1:a" & x & ",sheet2!a1:a" & x + y & ")")
    Sheets("sheet2").Rows("1:" & x).Delete
    [c1].Resize(UBound(a)) = a
End Sub

 

[YasserKhalil]

Thanks a lot Mr. Jindon for this great code ..That's wonderful
Is there a flexible way to deal with more sheets ..as in fact I intend to store values in five or more sheets ..
Best Regards

 

[YasserKhalil]

I found this solution but it deals with columns in a sheet

Function GetArrayFromColumns(SheetName As String, StartRow As Long, ParamArray Cols()) As Variant
    Dim LastRow As Long, C As Variant, Text As String, WS As Worksheet, MyArray() As String

    If SheetName = "" Then SheetName = ActiveSheet.Name
    Set WS = Worksheets(SheetName)

    For Each C In Cols
        LastRow = WS.Cells(Rows.Count, C).End(xlUp).Row
        Text = Text & Chr(1) & Join(WorksheetFunction.Transpose(WS.Range(WS.Cells(StartRow, C), WS.Cells(LastRow, C))), Chr(1))
    Next C

    GetArrayFromColumns = Split(Mid(Text, 2), Chr(1))
End Function

Sub Test()
    Dim Z As Long, MyArray() As String

    MyArray = GetArrayFromColumns("Sheet1", 2, "A", "E")
    For Z = LBound(MyArray) To UBound(MyArray)
        Debug.Print MyArray(Z)
    Next Z
End Sub

How to edit this code to deal with different sheets?

 

[jindon]

Sub test()
    Dim a, n As Long, e
    For Each e In Array("sheet1", "sheet2", "sheet3")
        a = GetData(a, e, "a", 1)
    Next
    a = Application.Transpose(a)
    [c1].Resize(UBound(a, 1)).Value = a
End Sub

Function GetData(a, ByVal ws As String, myCol As String, SRow As Long)
    Dim e, n As Long
    With Sheets(ws)
        For Each e In .Range(myCol & SRow, .Range(myCol & Rows.Count).End(xlUp)).Value
            If IsEmpty(a) Then
                ReDim a(0)
            Else
                ReDim Preserve a(UBound(a) + 1)
            End If
            a(UBound(a)) = e
        Next
    End With
    GetData = a
End Function

 

[YasserKhalil]

That's amazing ..That's exactly what I was searching for
Thank you very much Mr. Jindon
Best and Kind Regards

 

[Hui]

This can be done with formula and doesn't require VBA
http://www.get-digital-help.com/2009/06/29/merge-two-columns-into-one-list-in-excel/