CODEWITHSUNDEEP
vbaexcel
user27636
user27636

Reputation: 1090

Simple cut and paste excel VBA: inconsitent and strange results

I need to cut and paste a row array of non-empty values in an Active Sheet. "E5" is the first non-empty cell in the row array I want to cut. This array is just a sequence of numbers from 1:10 with no gaps. The desired behavior is to cut the sequence and past at "F5". The only complication is that I need the range to be copied is not fixed.

Tried:

Sub Update()             
     ActiveSheet.Range("E5", Range("5:5").End(xlToRight).Address).Copy
     ActiveSheet.Range("F5").Select
     ActiveSheet.Paste     
End Sub

I've had this code work once, or at least the "cut" part. Now, the resulting output for the whole code block is either:

  1. Error 1004 or 438
  2. Code cuts and pastes 1:2 to "F5"
  3. Cuts and pastes 1:1 to "F5"

Please note that before running the macro, I have always made sure that the array to be cut is exactly the same.

What's the proper method?

Truly, I've looked for a solution but all I found were complex or verbose macros. And VBA's structure is kind of opaque at first, at least to me.

Upvotes: 1

Views: 431

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

10 mins and no answer? This might me my lucky day :P

Is this what you are trying? I have commented the code so you shouldn't have any problem understanding it.

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lCol As Long

    '~~> Set this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Find the last column which has data in Col 5
        lCol = .Cells(5, .Columns.Count).End(xlToLeft).Column

        '~~> Directly cut paste
        .Range(.Cells(5, 5), .Cells(5, lCol)).Cut .Range("F5")
    End With
End Sub

Upvotes: 3

Related Questions