Copy few cells in row 1 thru to last row which is not completely blank

  • Thread starter Thread starter alienscript
  • Start date Start date
A

alienscript

Hi Vasant,

If I select any number of cells with formulas in row 1 (not just one
cell) and I want to copy down to the Last row which is not completely
blank in its 255 cells.

Hope you can help again to amend the code you have given me before.
Thanks very very much !


Sub CopyToEnd()
'
'Alienscript tested it works for cell N2.
'I realize sometimes I need to select few cells in row 1 and I do not
want to amend the code every now and then for each column at a time..


Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("N2").Copy Range("N3:N" & LastRow)
End Sub
 
Try something like:

Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Range("C2:N2").Copy Range("C3:N" & LastRow)
End Sub
 
Hi Vasant,

I forgot to tell you between column A to column CW in my worksheet,
there are only few columns of formulas. I only want to select cells in
these few columns to copy down to the last row that is not completely
blank in all columns (ie. its next immediate row will have all its 255
cells completely blank cells).

The rest of the columns contain all different data and values in all
the cells which I do not want to be copied.

This code copied all the data down, instead of copy only the cells I
selected in a few columns.

Is there a way to modify your code ?

Thanks in advance.
 
Let's try it again ...

Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Dim c As Range
For Each c In Selection
c.Copy Range(Cells(3, c.Column), Cells(LastRow, c.Column))
End Sub

For this to work, you will need to actually select the individual cells in
row 2 that you want to copy down.
 
Hi Vasant,

In my data worksheet, I selected 2 cells with formula in row 2 and run
the following code... it doesn't copy down !

Is there something I missed out ?


Sub MultipleCopyToLast()

Dim LastRow As Long
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Dim c As Range
For Each c In Selection
c.Copy Range(Cells(3, c.column), Cells(LastRow, c.column))

End Sub
 
Are you sure there's stuff in column B? As I recall, in your original
posting, you had designated column B which would determine the last row. So
the macro determines the last row by looking at the last entry in column B.

If you want to look at another column as the "anchor" column, simply change
the '2' in:

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

to the appropriate column number.

I'm not able to test it right now but I did test it before I posted the last
solution and it worked fine for me.

Or perhaps it's because during posting I accidentally deleted the Next
statement which should precede the End Sub line (I just noticed that),
although that should have given you a compile error.
 
Back
Top