CODEWITHSUNDEEP
vbaexcel
Adam
Adam

Reputation: 1295

Passing range as function(arguments)

I am trying to pass a range as a parameter in a function so that I can select and clear it.

However, I keep getting an Application defined/object defined error.

If I do this in my clearData method where I just do range("m3").select instead of range(test).select it works.

What am i doing wrong?

Sub CopyData()
    Call ClearData("Detailed Report", Sheets("Detailed Report").range("M3"))
    Sheets("Raw Data").Select
    range("A2").Select
    range(Selection, Selection.End(xlToRight)).Select
    range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Detailed Report").Select
    range("M3").Select
    ActiveSheet.Paste
End Sub

Sub ClearData(sheetName As String, range As range)
    Sheets(sheetName).Select
    Dim deletrange As range
    Set deletrange = range
    range(deletrange).Select
    deletrange(Selection, Selection.End(xlToRight)).Select
    deletrange(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
End Sub

Upvotes: 2

Views: 4053

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

Try not define variables with names like range, since Range is an Excel saved word.

Also, there's no need to Select and Selection , instead use fully qualified Ranges and worksheets.

Note: keep in mind that using End(xlDown) andEnd(xlToRight) to get the last row and column is not the safest way. If you want to use the continuous range with "M3", then use Range("M3").CurrentRegion.

If you want to get the entire sheet's range, including empty rows and columns in the middle, then use:

To get the Last row use:

Sheets("Detailed Report").Cells(Sheets("Detailed Report").Rows.Count, "M").End(xlUp).Row

To get the Last column use:

Sheets("Detailed Report").Cells(3, Sheets("Detailed Report").Columns.Count).End(xlToLeft).columnn

Code 

Sub CopyData()    
    ClearData "Detailed Report", Sheets("Detailed Report").Range("M3")        
    ' Copy >> Paste in 1 line (without Selecting)
    Sheets("Raw Data").Range("A2").Resize(Sheets("Raw Data").Range("A2").End(xlDown).Row, Sheets("Raw Data").Range("A2").End(xlToRight).Column).Copy _
    Sheets("Detailed Report").Range("M3")          
End Sub

'=====================================================================

Sub ClearData(sheetName As String, Rng As Range)
    ' the code line below uses your original method, less recommended
    Rng.Resize(Rng.End(xlDown).Row, Rng.End(xlToRight).Column).ClearContents        

    ' better of using the code line below:
    Rng.CurrentRegion.ClearContents

End Sub

Upvotes: 1

brettdj
brettdj

Reputation: 55702

Passing the sheet is overkill, you can pass just the range.

If you were to pass the sheet, pass it as an object, not a sheet name.

CurrentRegion also will suffice for what you want

Sub Better()
Dim rng1 As range
On Error Resume Next
Set rng1 = Sheets("Detailed Report").[m3]
On Error GoTo 0
If Not rng1 Is Nothing Then Call ClearData(rng1)
End Sub

clear code

Sub ClearData(rng1 As range)
rng1.CurrentRegion.ClearContents
End Sub

Upvotes: 1

Related Questions