VBA / Using Last column in formula

[Akbarov]

Hello Dear community,

I can use VBA to find last column index number. But I want to use it to paste formula to cell A1.
For example, instead of B2:D2 I want VBA to paste formula to A1 with last nonblank column name.
If last column is: 6 / I want formula to be B2:F2

Can anyone help me please?
Thanks in advance.

 

[DanteAmor]

Try:

Sub test()
  Dim lc As Long
  Dim rng As Range
  
  'use row 1 to find last column
  lc = Cells(1, Columns.count).End(xlToLeft).Column
  Set rng = Range("B2", Cells(lc, 2))
  
  'In the rng object you have the range of cells to use in your formula:
  MsgBox "Range: " & rng.Address
  
End Sub

 

[Akbarov]

Try:

Sub test()
  Dim lc As Long
  Dim rng As Range
 
  'use row 1 to find last column
  lc = Cells(1, Columns.count).End(xlToLeft).Column
  Set rng = Range("B2", Cells(lc, 2))
 
  'In the rng object you have the range of cells to use in your formula:
  MsgBox "Range: " & rng.Address
 
End Sub

Thank you for your time. 1 question.
I am trying to copy paste cell E3's adress. ( currently E3 = A1:D33 )
How can I copy paste that range in VBA? (A1:D33)