Consulting

Results 1 to 2 of 2

Thread: Roll column forward and hard paste previous

  1. #1

    Roll column forward and hard paste previous

    Hi,

    I have the following code to copy a formula forward to the next blank column and hard paste the previous one:

    Sub RollColumn()   Dim UsdCols As Long
       
       UsdCols = Cells(2, Columns.Count).End(xlToLeft).Column
       With Range(Cells(2, UsdCols), Cells(76, UsdCols))
          .Copy .Offset(, 1)
          .Value = .Value
    
    
    
    
       End With
        
    End Sub
    Is it possible to change the row 76 in the range to the last used row in that range?

    Thanks

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,535
    Sub RollColumn()
    Dim UsdCols As Long
    UsdCols = Cells(2, Columns.Count).End(xlToLeft).Column
    lastRow = Cells(Rows.Count, UsdCols).End(xlUp).Row
    With Range(Cells(2, UsdCols), Cells(lastRow, UsdCols))
      .Copy .Offset(, 1)
      .Value = .Value
    End With
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •