locked
Need help cleaning up my copy and paste code RRS feed

  • Question

  • I wrote the following code which copies data from cells (B2:D2) and pastes it all way down cols B:D until there is no data is col A. The code works but I feel it could use some cleaning up:

    Sub CopyRow2()

    Dim LastRow As Integer
    Dim x As Integer

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For x = 3 To LastRow
        If Cells(x, 1) <> "" Then
        
        With Sheets("Sheet1")
        .Range(.Range("B" & (x - 1)), .Range("B" & (x - 1)).End(xlToRight)).Copy
        
        End With
        
        Cells(x, 2).Select
        ActiveSheet.Paste
        
        End If
        
        Next x
        
        Application.CutCopyMode = False
        Range("A1").Select
       
    End Sub

    Any suggestions?

    Tuesday, February 23, 2016 7:10 PM

Answers

  • Try:

    Range("B2:D2").AutoFill Destination:=Range("B2:D" & Range("A2").End(xlDown).Row)

    In Excel, if you select B2:D2 then double-click the small black square bottom right corner of the cursor the data will fill automatically. The above code does the same thing.

    The simplest VBA code often comes from knowing your application well including all shortcuts. I recorded  double-clicking the little square then edited the recorded code to get the above. So read tips on shortcuts etc.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz


    Tuesday, February 23, 2016 8:09 PM
  • Assuming that column A contains constants (not formulas):

    Sub CopyRow2()
        Dim LastRow As Long
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Range("B2:D2").Copy Range("A3:A" & LastRow) _
            .SpecialCells(xlCellTypeConstants).Offset(0, 1)
        Application.CutCopyMode = False
     End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 23, 2016 8:17 PM

All replies

  • Try:

    Range("B2:D2").AutoFill Destination:=Range("B2:D" & Range("A2").End(xlDown).Row)

    In Excel, if you select B2:D2 then double-click the small black square bottom right corner of the cursor the data will fill automatically. The above code does the same thing.

    The simplest VBA code often comes from knowing your application well including all shortcuts. I recorded  double-clicking the little square then edited the recorded code to get the above. So read tips on shortcuts etc.


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz


    Tuesday, February 23, 2016 8:09 PM
  • Assuming that column A contains constants (not formulas):

    Sub CopyRow2()
        Dim LastRow As Long
        LastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Range("B2:D2").Copy Range("A3:A" & LastRow) _
            .SpecialCells(xlCellTypeConstants).Offset(0, 1)
        Application.CutCopyMode = False
     End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 23, 2016 8:17 PM
  • Thanks. The only thing is I don't want to reference Col. D in my code because it's possible I will add more columns. So I used xlToRight 
    Tuesday, February 23, 2016 8:26 PM