For the millionth time... Trying everthing I can think of the get the last occupied column in a row using a cell in the row.

[OaklandJim]

Oy! I just cannot get the syntax correct to find the last occupied column in a row where the row is the row that a specified cell is located in. What simple thing am I doing incorrectly?

    Set wsChart = ThisWorkbook.Worksheets("Sheet2") '<= worksheet containing the chart

    Set rAnchorCellChartData = wsChart.Range("C2")  '<= this is the cell where chart is located (in wsChart)
   
    With rAnchorCellChartData
   
'This yields Sheet2 which is correct
Debug.Print "rAnchorCellChartData.Parent = " & .Parent.Name

'This yields $C$2 which is correct.
Debug.Print "rAnchorCellChartData address = " & .Address

'This yields $B$2:$F$3 which is correct. Column B holds data labels.
Debug.Print "Data range = " & .CurrentRegion.Address
   
'       Yields zero
        'iColumns = .Cells(1, wsChart.Columns.Count).End(xlToLeft).Column - .Column
       
'       Yields zero
        'iColumns = .Cells(1, Columns.Count).End(xlToLeft).Column - .Column
       
'       Yields zero
        'iColumns = .Offset(0, 10000).End(xlToLeft).Column - .Column
       
'       Yields 16383
'       iColumns = .End(xlToRight).Column - .Column
   
    End With

 

[DanteAmor]

Try:

Sub test_last()
  Dim wsChart As Worksheet
  Dim rAnchorCellChartData As Range
  Dim iColumns As Long
  
  Set wsChart = ThisWorkbook.Worksheets("Sheet2") '<= worksheet containing the chart
  Set rAnchorCellChartData = wsChart.Range("C2")
  With rAnchorCellChartData
    iColumns = wsChart.Cells(.Row, wsChart.Columns.Count).End(xlToLeft).Column
  End With
End Sub

Or simply:

Sub lastcolumn()
  Dim lc As Long
  lc = Sheets("Sheet2").Cells(2, Columns.Count).End(xlToLeft).Column
End Sub

 

[OaklandJim]

Hey DanteAmor. Thanks very much for that!