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.

 

[adamsm]

One more help If I may ask.

How could the code be adjusted so that it works on a filtered sheet?

Suppose the first row value of column "Q" before filtering is 0001 and last data row is 0004;

When the sheet is filtered the first row value is 0002 and the last data row is 0003.

Having this situation; how could the code be adjusted so that it displays the first visible row and last visible data row of column "Q"

I hope I've made my question clear.

Any help on this would be kindly appreciated.

 

[MrKowz]

Try:

Public Sub CopyFirstLast()
Dim i   As Long, _
    LR  As Long
    
LR = Range("Q" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 17 To LR
    If Rows(i).Hidden = False And Range("Q" & i).Value <> "" Then
        Range("Q" & i).Copy Destination:=Range("A1")
        Exit For
    End If
Next i
Range("Q" & LR).Copy Destination:=Range("A2")
Application.ScreenUpdating = True
End Sub