- Code: Select all
Set Rng = Range("A1:B10")
- Code: Select all
Dim Column_start As String, Dim Row_start As Long
Dim Column_end As String, Dim Row_end As Long
Set Rng = Range(Column_start & Cstr(Row_start) & ":" & Column_end & Cstr(Row_end))
- Code: Select all
Set Rng = Range(Cells(1, 1), Cells(10, 2))
- Code: Select all
With Worksheets("Sheet1")
Set Rng = Range(.Cells(1, 1), .Cells(10, 2))
End With
' or
Set Ws = Worksheets("Sheet1")
Set Rng = Range(Ws.Cells(1, 1), Ws.Cells(10, 2))
In either case making this formula dynamic is much easier than the example modelled on the spreadsheet interface syntax, and be it only because there is no more need to deal with strings. This is the same specification as above but re-written in the other syntax:-
- Code: Select all
Dim C_start As Long, Dim R_start As Long
Dim C_end As Long, Dim R_end As Long
Set Rng = Range(Cells(R_start, C_start), Cells(R_end, C_end))
The object of creating a function to replace this code is to still shorten it - making it still easier to read - while at the same time adding durability and flexibility. The function here introduced requires the call below to achieve the same result as the above examples:-
- Code: Select all
Set Rng = SetRange(R_start, C_start , R_end, C_end)
- Set Rng = SetRange returns A1. This is because there is no row 0 or column 0. Therefore the function replaces 0 with 1. So, if your calculation of R-start or C-start returns the wrong result (anything smaller than 1 would be wrong) the function still returns a range, rather than an error. This enhances the stability of your code under circumstances that might be beyond your control.
- Set Rng = SetRange(4) returns A4. Since only R_start is specified in the call, C_start is presumed to be 1.
- The same thing works for the column. Set Rng = GetRange(, 4) returns D1.
- Set Rng = SetRange(2, 2, 20) returns B2:B20. This is because the function presumes C_end to be equal to C_start if it isn't supplied. The same works for R_end and R_start.
- Set Rng = SetRange(2, 2, , 26) doesn't specify R_end. Therefore B2:Z2 will be returned where R_end equals R_start.
- The function is also capable of returning a range from another worksheet. Just add the sheet object to the function call.
- Code: Select all
Set Rng = SetRange(R_start, C_start, R_end, C_end, Sheets("Sheet3"))
Here is the function's fully commented version.
- Code: Select all
Function SetRange(Optional ByVal Rstart As Long = 1, _
Optional ByVal Cstart As Long = 1, _
Optional ByVal Rend As Long, _
Optional ByVal Cend As Long, _
Optional Ws As Worksheet) As Range
'' 0088 V 1.0
' ==================================================
' Parameters:
' Rstart = First worksheet row included in the range
' [Optional: = 1 if not specified]
' Cstart = First worksheet column included in the range
' [Optional: = 1 if not specified]
' Rend = Last worksheet row included in the range
' [Optional: = Rstart if not specified]
' Cend = Last worksheet column included in the range
' [Optional: = Cstart if not specified]
' Ws = The worksheet on which the range is to be created
' [Optional: = ActiveSheet, if not specified]
' ==================================================
' Substitute 1 for erroneous Rstart or Cstart values
With Excel.WorksheetFunction
Rstart = .Max(1, Rstart)
Cstart = .Max(1, Cstart)
End With
' Rend and Cend equal the start parameters if unspecified or negative
If Rend < 1 Then Rend = Rstart
If Cend < 1 Then Cend = Cstart
If Ws Is Nothing Then Set Ws = ActiveSheet
Set SetRange = Range(Ws.Cells(Rstart, Cstart), Ws.Cells(Rend, Cend))
End Function
In my own projects I don't normally need the comments and prefer to use what I call a stripped version that doesn't need as much space.
- Code: Select all
Function SetRange(Optional ByVal Rstart As Long = 1, Optional ByVal Cstart As Long = 1, Optional ByVal Rend As Long, Optional ByVal Cend As Long, Optional Ws As Worksheet) As Range
' 0088 V 1.0
With Excel.WorksheetFunction
Rstart = .Max(1, Rstart)
Cstart = .Max(1, Cstart)
End With
If Rend < 1 Then Rend = Rstart
If Cend < 1 Then Cend = Cstart
If Ws Is Nothing Then Set Ws = ActiveSheet
Set SetRange = Range(Ws.Cells(Rstart, Cstart), Ws.Cells(Rend, Cend))
End Function
By the way, here is a little trick to determine the number of a column:
- Code: Select all
C_end = Range("BF1").Column