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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
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?
 

jmthompson

Well-known Member
Joined
Mar 31, 2008
Messages
966
Do you want the entire row copied or the first and last used cell in column Q excluding rows 1-16?
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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.)
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
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?
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
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?
 

MrKowz

Well-known Member
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,847
Messages
5,708,950
Members
421,601
Latest member
Garlo

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
Top