Referring to same column that contains UDF

[MrKowz]

Hey all,

I'm writing a UDF for a user on the boards here, and I have the need for the UDF to identify what column it is located in. Is there a way to do this without requiring an additional argument in the code?

Thanks in advance!

 

[Juan Pablo González]

Use the .Cells property

first cell

rng.Cells(1,1)

last cell

rng.Cells(rng.Rows.Count, 1)

assuming a contiguous range of course...

 

[MrKowz]

Use the .Cells property

first cell

rng.Cells(1,1)

last cell

rng.Cells(rng.Rows.Count, 1)

assuming a contiguous range of course...

Perfect, thanks!

 

[RoryA]

rng(rng.count)

or if your range might be discontiguous:

Function LastCellInRange(rngIn As Range) As Range
   With rngIn.Areas
      If .Count > 1 Then
         With .Item(.Count)
         Set LastCellInRange = .Cells(.Count)
         End With
      Else
         Set LastCellInRange = rngIn(rngIn.Count)
      End If
   End With
   
End Function

 

[shg]

Hmmm ...

Sub demo()
    MsgBox LastCellInRange(Columns("E")).Address
    MsgBox LastCell(Columns("E")).Address
End Sub
 
Function LastCellInRange(rngIn As Range) As Range
   With rngIn.Areas
      If .Count > 1 Then
         With .Item(.Count)
         Set LastCellInRange = .Cells(.Count)
         End With
      Else
         Set LastCellInRange = rngIn(rngIn.Count)
      End If
   End With  
End Function
 
Function LastCell(r As Range) As Range
    With r.Areas(r.Areas.Count)
        Set LastCell = .Cells(.Cells.Count)
    End With
End Function

 

[RoryA]

Should probably have tested. (or drunk less at lunch):

Function LastCellInRange(rngIn As Range) As Range
   With rngIn.Areas
      If .Count > 1 Then
         With .Item(.Count)
         Set LastCellInRange = .Cells(.Cells.Count)
         End With
      Else
         Set LastCellInRange = rngIn.Cells(rngIn.Cells.Count)
      End If
   End With
End Function

 

[MrKowz]

Should probably have tested. (or drunk less at lunch):

I always thought you were in a perpetual state of inebriation.

 

[RoryA]

Oh no. I try to sober up at least once a week.