title: .End(xlToRight) within a range
body: <p>I've just got a quick one wondering if I can somehow alter the following snippet to include <code>.End(xlToRight)</code> instead of defining <code>L6</code> (the result is the same).</p>
  
  <pre><code>Sub Test()
  Dim LastCol As String
  
  With Worksheets("Sheet1")
      LastCol = .Cells(5, .Columns.Count).End(xlToLeft).Address
      .Range(Range("A5"), LastCol).Copy    
  
      .Range("B5:L5", Range("B5:L5").Offset(LastRow - FirstRow, 0)).PasteSpecial xlPasteFormulas
  
      .Range("B6", .Cells.SpecialCells(xlCellTypeLastCell)).Copy
      .Range("B6").PasteSpecial xlPasteValues
  End With
  End Sub
  </code></pre>
  
  <p>Many thanks for any help :)</p>
  
  <p><strong>EDIT: updated pdw TonyDallimore <em>(see response below)</em></strong></p>
  
  <p><em>Tony, the above code is the sum of what I've been trying to get working. Within the <code>with</code> statement, I'm looking to copy the contents of row 5, and paste them down to the nth row - which is defined by a list already present in columnA. The last line per your advice will then paste the values of all but the top row (r5) to preserve transparency for the user, while minimising file size.
  The middle bit is the remaining 'work in progress', as L5 is not certain to be the farmost column.</em></p>