Copy and paste first and last data row

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Hi anyone,

How could I figure out a code that would copy the first and last data row below the row 16 of column "Q" to cell A1 and B1.

Any help on this would be kindly appreciated.

Thanks in advance.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try:

Code:
Public Sub CopyFirstLast()
Range("Q16").End(xlDown).EntireRow.Copy Destination:=Range("A1")
Range("Q" & Rows.Count).End(xlUp).EntireRow.Copy Destination:=Range("A2")
End Sub
 
Upvote 0
Thanks for the help. But the code copies the first and the last data row of column "Q" to Q1 & Q2.

Also it copies the row 17 value of any column where data exists to the row 1 of that column.

How shall I avoid this?
 
Upvote 0
Do you want the entire row copied or the first and last used cell in column Q excluding rows 1-16?
 
Upvote 0
Thanks for the help. But the code copies the first and the last data row of column "Q" to Q1 & Q2.

Also it copies the row 17 value of any column where data exists to the row 1 of that column.

How shall I avoid this?

Did you adjust the code in any way? It works fine on my end (finds the first data row, according to column Q, copies that entire row to A1. Then finds the last data row, according to column Q, and copies that entire row to A2.)
 
Upvote 0
No I did not change the code. But my intention is to copy the first and last used cell in column Q excluding rows 1-16.

How may I achieve this?
 
Upvote 0
We just needed to take out the .EntireRow part:

Code:
Public Sub CopyFirstLast()
Range("Q16").End(xlDown).Copy Destination:=Range("A1")
Range("Q" & Rows.Count).End(xlUp).Copy Destination:=Range("B1")
End Sub
 
Upvote 0
The code works fine if there is no column Header in row 16. Meaning it copies the column 17 and the last data row from column "Q".

But if I place a column header in column Q row 16 the code only copies the last data row to cell A1 and B1.

How shall I overcome this?
 
Upvote 0
Code:
Public Sub CopyFirstLast()
If Range("Q17") = "" Then
    Range("Q16").End(xlDown).Copy Destination:=Range("A1")
Else
    Range("Q17").Copy Destination:=Range("A1")
End If
Range("Q" & Rows.Count).End(xlUp).Copy Destination:=Range("B1")
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top