CODEWITHSUNDEEP
vbaexcel
phillipsK
phillipsK

Reputation: 1516

Excel function broke

How could I get the following function to work?

Public Function DataRange(Somesheet as String, Optional StartCell as String) as Excel.Range
    If StartCell = "" Then
       DataRange =ThisWorkbook.Sheets( Somesheet).Range( ThisWorkbook.Sheets( Somesheet)_ 
                  .Cells(1,1).End(xldown),ThisWorkbook.Sheets(Somesheet).Cells(1,1).End(xlright))
    Else
       DataRange = ThisWorkbook.Sheets(Somesheet).Range(ThisWorkbook.Sheets(Somesheet)_
                   .Range(Startcell).End(xldown),ThisWorkbook.Sheets(Somesheet).Range(Startcell).End(xlright))
    End If

End Function

Upvotes: 0

Views: 69

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

Public Function DataRange(Somesheet as String,  _
                          Optional StartCell as String = "A1") as Excel.Range

   With ThisWorkbook.Sheets(Somesheet)
       Set DataRange = .Range(.Range(Startcell).End(xldown), _
                              .Range(Startcell).End(xltoright))
   End With

End Function

Note: xlToRight not xlRight

Usage:

Dim rng
Set rng = DataRange("Sheet1")         'uses default A1 as top-left cell
Set rng = DataRange("Sheet1", "E10")  'uses E10

This might work just as well:

ThisWorkbook.Sheets(Somesheet).Range(Startcell).CurrentRegion

Upvotes: 1

Mark Moore
Mark Moore

Reputation: 510

I am assuming your If StartCell = "" is not working. Change the Startcell variable to be a variant, this will then allow you to use the ismissing function to check if has been passed and assign your own default if it hasn't. IsMissing only works with variant data types. If needed use Cvar() when passing the parameter to the functions

Upvotes: 1

Related Questions