Hi
If I use multiple range selection as ("B2:B1000, G2:G1000, L2:L1000, Q2:Q1000"), the macro works fine. However, when I try to use ("B2:B" & LastRow, "G2:G" & LastRow.....) I get an error on that line. LastRow was correctly defined. I also tried using the Union.

The code will create a list from given ranges in "Sheet2" and write to "Result2" Column A along with a count of duplicates in column B My knowledge in VBA is minimal. Any help is appreciated. Many thanks in advance.
Note: I'm not sure of the original poster of the code, bad of me.

Sub Testing()
    Dim vCell As Range
    Dim vRng() As Variant
    Dim i As Integer
'    Dim xRng As Range
'    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
    
    ReDim vRng(0 To 0) As Variant

    Sheets("Result2").Cells.Delete
    Sheets("Sheet2").Select
    LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    Cells(15, 1) = LastRow   ' to see LastRow is generated

      For Each vCell In Sheets("Sheet2").Range("B2:B10,G2:G10,L2:L10,Q2:Q10")

        If vCell.Value <> "" Then
            ReDim Preserve vRng(0 To i) As Variant
            vRng(i) = vCell.Value
            i = i + 1
        End If
    Next

    vRng = CountDuplicates(vRng)

    Sheets("Result2").Select
    Range(Cells(1, 1), Cells(UBound(vRng), UBound(vRng, 2))) = vRng
    Rows(1).Insert
    Range("A1:B1") = Array("Name", "Entries")
    ActiveSheet.UsedRange.Sort Range("B1"), xlDescending
End Sub