How to copy cells in a row with varying lengths using VBA

Question

I'm trying to copy cells in a row but every row will have varying lengths. Eg: Row1 has data from A1:H1 Row2 has data from A2:L2 Row3 has data from A3:D3 The usual code would be .Range(.Cells(i,"A"),.Cells(i,"H")).Copy But in a loop how do I make this detect the length of the row??

Thanks!

"Length of a row"? Do you mean the last column, to the right? To get the last cell with data, in a row (let's use row 5, for example), you can do lastCol = Cells(5,Columns.Count).End(XlToLeft).Column, which gives you the last column. Then, it's just myRowRange = Range(Cells(5,1),Cells(5,lastCol)). — BruceWayne

Sergej Sergej · Accepted Answer · 2016-04-25T16:18:07

Try this:

Sub CopyRows
    Dim intCounter as Integer
    For intCounter = 1 to Cells(Rows.Count,1).End(xlUp).Row
        Range(Cells(intcounter,1), Cells(intcounter,Columns.Count).End(xlToLeft).Column)).Copy
        ' Copy your stuff, where you want
    Next intCounter
End sub

And in your specific case:

.Range(.Cells(i,1),.Cells(i,.Cells(i,Columns.Count).End(xlToLeft).Column)).Copy

How to copy cells in a row with varying lengths using VBA

1 Answers