How to get the opposite cell details from range in VBA?

[mehidy1437]

Hi

If my selection range is a1: e5 then with below, we can have the first cell as fCell is a1, and last cell as lCell is e5.
But what if, i want to get the first column last cell as fColLastCell is a5, and last column first cell as lColFirstCell is e1?

Range can be vary time to time. Its not fixed.
How can I grab this in VBA?

  Set Rng = Selection
  fCell = Rng.Cells(1).Value
  lCell = Rng(Rng.count).Value
  ACellAdd = ActiveCell.Address(0, 0)
  lCellAdd = Rng(Rng.count).Address(0, 0)

 

[Fluff]

how about

    Dim TLeft As String, TRight As String, BLeft As String, BRight As String
    
    With Selection
        TLeft = .Cells(1, 1)
        TRight = .Cells(1, .Columns.Count)
        BLeft = .Cells(.Rows.Count, 1)
        BRight = .Cells(.Rows.Count, .Columns.Count)
    End With

 

[JoeMo]

Maybe:

Sub FirstAndLast()
Dim R As Range, Ar As Range
Set R = Selection
If R.Areas.Count = 1 Then  'R is a range of contiguous cells
    'first cell of R
    MsgBox R(1).Address
    'last cell of R
    MsgBox R(R.Count).Address
    'first cell of 2nd column
    MsgBox R.Columns(2).Cells(1).Address
    'last cell of 2nd column
    MsgBox R.Columns(2).Cells(R.Columns(2).Cells.Count).Address
Else  'R is a range of non-contiguous cells
    'first cell of R
     MsgBox R(1).Address
     'last cell of R
     MsgBox Cells(R.Areas(R.Areas.Count).Row, _
         R.Areas(R.Areas.Count).Column)(R.Areas(R.Areas.Count).Count).Address
End If
End Sub

 

[mehidy1437]

how about

    Dim TLeft As String, TRight As String, BLeft As String, BRight As String
   
    With Selection
        TLeft = .Cells(1, 1)
        TRight = .Cells(1, .Columns.Count)
        BLeft = .Cells(.Rows.Count, 1)
        BRight = .Cells(.Rows.Count, .Columns.Count)
    End With

Thanks.

 

[Fluff]

You're welcome

 

[mehidy1437]

Maybe:

Sub FirstAndLast()
Dim R As Range, Ar As Range
Set R = Selection
If R.Areas.Count = 1 Then  'R is a range of contiguous cells
    'first cell of R
    MsgBox R(1).Address
    'last cell of R
    MsgBox R(R.Count).Address
    'first cell of 2nd column
    MsgBox R.Columns(2).Cells(1).Address
    'last cell of 2nd column
    MsgBox R.Columns(2).Cells(R.Columns(2).Cells.Count).Address
Else  'R is a range of non-contiguous cells
    'first cell of R
     MsgBox R(1).Address
     'last cell of R
     MsgBox Cells(R.Areas(R.Areas.Count).Row, _
         R.Areas(R.Areas.Count).Column)(R.Areas(R.Areas.Count).Count).Address
End If
End Sub

Sorry, it doesn't give me the desired result.

 

[Scott Huish]

You could also leave them as ranges, then you could work with them in the same way:

Type RangeParts
    EntireRange As Range
    TLeft As Range
    TRight As Range
    BLeft As Range
    BRight As Range
End Type

Sub Test()
Dim myRng As RangeParts
Set myRng.EntireRange = Selection

  With myRng.EntireRange
        Set myRng.TLeft = .Cells(1, 1)
        Set myRng.TRight = .Cells(1, .Columns.Count)
        Set myRng.BLeft = .Cells(.Rows.Count, 1)
        Set myRng.BRight = .Cells(.Rows.Count, .Columns.Count)
    End With
    
myRng.BRight.Select

End Sub