Copy only part of last row that has data

Nachtschade

New Member
Joined
Dec 6, 2013
Messages
25
I have this code:

Code:
Range("A" & Rows.Count).End(xlUp).EntireRow.Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.PasteSpecial
It copies the entire last row that contains data and pastes it one row below.

I only want it to copy part of the row, columns A to I to be exact. How do I edit this?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
See next code ... NOT TESTED

Code:
Cells(rows.count,"A").end(3).resize(1,9).Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.PasteSpecial
 
Last edited:

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,112
Try:

Code:
Dim lastrow As Long
Dim sht As Worksheet


Set sht = ActiveSheet


lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


Range("A" & lastrow & ":I" & lastrow).Copy
Range("A" & lastrow + 1).PasteSpecial
 

PCL

Well-known Member
Joined
Jul 15, 2008
Messages
1,348
a smarter one

Code:
    With Cells(Rows.Count, "A").End(3)
        .Resize(1, 9).Copy
        .Offset(1).PasteSpecial
    End With
 

Nachtschade

New Member
Joined
Dec 6, 2013
Messages
25
See next code ... NOT TESTED

Code:
Cells(rows.count,"A").end(3).resize(1,9).Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).EntireRow.PasteSpecial

Thanks a lot.
 

Nachtschade

New Member
Joined
Dec 6, 2013
Messages
25
I also have another question. Not sure if I should post that here or make a new topic but anyway..

I want to copy an entire column* with offset where the offset is x columns to the right and x is a cell value. The paste needs to be as value.

*Where I say entire column what I actually mean is only the cells in that contain a value in that column (there's always exactly 40 cells with a value).
 

Forum statistics

Threads
1,089,223
Messages
5,406,948
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top