how to make general range

[petroj02]

Hello,

I have few collumns with data and this data I want to coppy to different workbook only once. I am trying to make this code for Sub with General variable range. the part of range I want to Change is named myRng. Unfortunately it doesnt work and gives me an error message and I am not able to solve this...

        Sub setRange (byval rng as range, wb as workbook)
        Dim d As Object, c As Variant, i As Long, lr As Long
        dim myRng as range
        set myRng = Range(rng)
        Set d = CreateObject("Scripting.Dictionary")                                
        lr = ThisWorkbook.Sheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Row     
                c = ThisWorkbook.Sheets(sheetName).Range(myRng& lr)            
        For i = 1 To UBound(c, 1)                                                   
            d(c(i, 1)) = 1                                                          
        Next i
        For i = 3 To 100
            If .Sheets("List1").Cells(i, 3) = "" Then
                x = i
                Exit For
            End If
        Next
        wb.Sheets("List1").Cells(x, 3).Resize(d.Count) = Application.Transpose(d.Keys)

so I have to implement this twice

        Dim d As Object, c As Variant, i As Long, lr As Long
        Set d = CreateObject("Scripting.Dictionary")                                
         lr = ThisWorkbook.Sheets(sheetName).Cells(Rows.Count, 1).End(xlUp).Row     
                 c = ThisWorkbook.Sheets(sheetName).Range("BK5:BK& lr)            
         For i = 1 To UBound(c, 1)                                                   
             d(c(i, 1)) = 1                                                          
         Next i
         For i = 3 To 100
             If .Sheets("List1").Cells(i, 3) = "" Then
                 x = i
                 Exit For
             End If
         Next
         .Sheets("List1").Cells(x, 3).Resize(d.Count) = Application.Transpose(d.Keys)

        Set d = CreateObject("Scripting.Dictionary")                                    
              c = ThisWorkbook.Sheets(sheetName).Range("BL5:BL& lr)            
         For i = 1 To UBound(c, 1)                                                   
             d(c(i, 1)) = 1                                                          
         Next i
         For i = 3 To 100
             If .Sheets("List1").Cells(i, 3) = "" Then
                 x = i
                 Exit For
             End If
         Next
         .Sheets("List1").Cells(x, 3).Resize(d.Count) = Application.Transpose(d.Keys)

[mana]

What's the purpose to use dictionary?

[petroj02]

actaully, I found this part of code somewhere on Internet. I dont even know whats the dictonary doing exactly... It is on higher skill Level than I actually have.. But It should take from my main workbook data at specific column. (this data could be in column multiple times) And take this data to new workbook but only once... this is the reason of using dictionary. but I have found that this solution isnt the best one because, if there is empty cell in column, it wont convert data that follows after this cell...

[mana]

??

Dim d As Object
Dim v
Dim i As Long, j As Long
  
Set d = CreateObject("Scripting.Dictionary")

v = rng.Value

For i = 1 To UBound(v, 2)
    For j = 1 To UBound(v, 1)
        If v(i, j) <> "" Then d(v(i, j)) = 1
    Next
Next

wb.Sheets("List1").Cells(Rows.Count, "C").End(xlUp).Offset(1).Resize(d.Count) _
    = Application.Transpose(d.Keys)

[mikerickson]

You've already declared rng to be a range object, you don't need to use the Range property.

Sub setRange (byval rng As range, wb As workbook) 
    Dim d As Object, c As Variant, i As Long, lr As Long 
    Dim myRng As range 

    ' Set myRng = Range(rng): rem not this

    Set myRng = rng: Rem this instead

' ...
End Sub