Copy and paste first and last data row

[adamsm]

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.

 

[MrKowz]

Try:

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]

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]

Do you want the entire row copied or the first and last used cell in column Q excluding rows 1-16?

 

[MrKowz]

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]

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]

We just needed to take out the .EntireRow part:

Public Sub CopyFirstLast()
Range("Q16").End(xlDown).Copy Destination:=Range("A1")
Range("Q" & Rows.Count).End(xlUp).Copy Destination:=Range("B1")
End Sub

 

[adamsm]

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]

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

 

[adamsm]

Thanks for the help. I do really appreciate it.