asked on

excel vba subroutine to return a range

In my main Sub, I am copying a 1 columned range.

.range.copy

I would like to then call the following sub, which will return me back a different range, r, which I will use in my main sub.
What is the correct way to call DupeKiller and return back r?

Sub DupeKiller()
Dim wsTemp As Worksheet, lRows As Long, rTemp As Range
Set wsTemp = ThisWorkbook.Worksheets("Temp")
    With wsTemp
        .Range("A1").PasteSpecial xlPasteValues
        lRows = .Range("A" & Rows.Count).End(xlUp).Row
        Set rTemp = .Range("A1:A" & lRows)
        With rTemp
            .RemoveDuplicates Columns:=1, Header:=xlNo
        End With
        lRows = .Range("A" & Rows.Count).End(xlUp).Row
        Set rTemp = .Range("A1:A" & lRows)
        Set r = rTemp
        rTemp.ClearContents
    End With
End Sub

Open in new window

Ken Butters

I would change it to a function that returns a range value.
Below I am showing an example of calling the function.

Another way would be to use a public variable that both routines could access.

To set the return value for a function , instead of using 'r' you would have to use the name of the function. That is why I changed the line of code from "Set r = rTemp" to "Set DupeKiller = rTemp"

Sub main()
   
   Dim myRange as Range
   Set myRange = DupKiller() 

End Sub


Function DupeKiller() as Range
Dim wsTemp As Worksheet, lRows As Long, rTemp As Range
Set wsTemp = ThisWorkbook.Worksheets("Temp")
    With wsTemp
        .Range("A1").PasteSpecial xlPasteValues
        lRows = .Range("A" & Rows.Count).End(xlUp).Row
        Set rTemp = .Range("A1:A" & lRows)
        With rTemp
            .RemoveDuplicates Columns:=1, Header:=xlNo
        End With
        lRows = .Range("A" & Rows.Count).End(xlUp).Row
        Set rTemp = .Range("A1:A" & lRows)
        Set DupeKiller = rTemp
        rTemp.ClearContents
    End With
End Function

Open in new window

newparadigmz

ASKER

Exiting this function does not retain the values inside the range.

It could be a range or an array with the values. I just need something to pass back to the main sub.

Ken Butters

Not sure what you mean by "it does not retain the values inside the range".

A range is a group of cells. It can be a column, it can be a row, it can be just 5 cells selected that are part of a column. Each cell in a range of cells is going to have some value, whether that is a text string or a NULL or whatever value. By selecting or identifying a cell or a group of cells, it cannot "lose" its value.

What your original question asked is... "What is the correct way to call DupeKiller and return back r?" What I did was modify your routine so that it would return an object known as a range. That range is a group of one or more cells.

That being said... If the Function is not working the way you expected it to, then I'll need you to expand on your question a little in order to identify specifically what behavior you are expecting upon return from the function and/or Subroutine. Once we know what you are expecting, we can change the code accordingly.

newparadigmz

ASKER

In the attached file, running sub main, I need avRange populated with values.

Thanks
Book1.xlsm

ASKER CERTIFIED SOLUTION

Ken Butters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newparadigmz

ASKER

thank you very much!
i was confusing the reference to the range with the contents of the range.