Solved

excel vba subroutine to return a range

Posted on 2013-10-30
6
634 Views
Last Modified: 2013-11-01
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

0
Comment
Question by:newparadigmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39612811
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

0
 

Author Comment

by:newparadigmz
ID: 39613999
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.
0
 
LVL 19

Expert Comment

by:Ken Butters
ID: 39614265
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.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:newparadigmz
ID: 39614816
In the attached file, running sub main, I need avRange populated with values.

Thanks
Book1.xlsm
0
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 39615346
Here is what your code does in DupeKiller

Step 1 Copy Column 'A' data from "Not Temp" worksheet to "Temp" worksheet.
Step 2 Remove Duplicate items from list in "Temp" Worksheet"
Step 3 Sets a new Range that is equal to the length of Column with Duplicates removed
Step 4 Sets Dupkiller return value to that new range object
Step 5 "Rtemp.clearcontents"  this command deletes all the values you just caclulated in the "Temp" worksheet.
Step 6 upon return to Main... you set the aVrange equal to return value from function. (but now that range as been cleared of data.)

So this code has 2 basic changes

(1) I removed the clearContents in the DupeKiller function
(2) I added a 2 msgbox calls in the Main routine to display values from avRange.

(NOTE: avRange.values are really just cells values from worksheet "Temp")

so if you clear the temp worksheet values, you in effect clear avRange.values as well.

Option Explicit

Sub main()
   Dim myRange As Range, avRange As Variant
   ThisWorkbook.Worksheets("Not Temp").Range("A1:A12").Copy
   Set myRange = DupeKiller()
   avRange = myRange.Value
   MsgBox "Cell A1=" & avRange(1, 1)
   MsgBox "Cell A2=" & avRange(2, 1)

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
    End With
End Function

Open in new window

0
 

Author Closing Comment

by:newparadigmz
ID: 39616860
thank you very much!
i was confusing the reference to the range with the contents of the range.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question