Copy second row until last row with blanks in a column after filtering data

Question

I hope i can explain this well. I am having a difficulty in my code and what code should i use. I have a big data, that needs to be filter first. and the range is not consistent.

after filtering data, i have to copy the second row (this is not to copy the Column name), until the last row with blanks.

I tried this code, but it didn't work

Sheets("Big5").Select
Range("P1").Select

Dim testlrow As Long
testlrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Dim rngBIGcode As Range
Set rngBIGcodeM = Range(Cells(ActiveCell.Row + 1, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column))
    rngBIGcodeM.SpecialCells(xlCellTypeVisible).Cells(1).Select
    Range(Selection, Selection.End(xlDown) & testlrow).Select

I have to copy the second row from P1, until the last row.

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Apr 26 '19 at 13:07

score 0 · Accepted Answer · answered Apr 26 '19 at 13:35

There are a few problems with your code, starting with using Select and ActiveCell. You also declare your range, then use a different name to Set. One way to ensure your variables are properly declared is to type Option Explicit above your Sub. Then it will verify your variables. Ensure that your objects are well-defined by using the worksheet variable in front of the cells. This is how your code could work:

   Sub Test ()
        Dim ws As Worksheet
        Dim testlrow As Long
        Dim rngBIGcodeM as Range

        Set ws = Sheets("Big5")
        testlrow = ws.Cells(Rows.Count, "P").End(xlUp).Row

        Set rngBIGcodeM = ws.Range(ws.Cells(2, "P"), ws.Cells(testlrow, "P"))
        rngBIGcodeM.SpecialCells(xlCellTypeVisible).Copy 'Enter Destination Here

        Application.CutCopyMode = False

    End Sub

Copy second row until last row with blanks in a column after filtering data

1 Answers1