VBA to copy data displayed in a FILTER function?

[helpneeded2]

I am not very experienced with VBA, and I am trying to use code from another sheet I made in which it was coping only the data that is visible on the sheet.

Sub rangeSelection()
    Dim endOfRows As Long
    endOfRows = Range("A25003").Value
    Range("A4").Resize(endOfRows, 69).Select
    Selection.Copy
End Sub

On the new sheet I am creating, the sheet may have up to 25000 rows of data, across 69 columns. There will be no blanks rows of data in between the rows of data. The raw data pasted in will vary between 1000 rows to 25000 rows.

When I try to run the above formula I get an error message:

Run-time error '1004':

Application-defined or object-defined error

The source sheet has the following filter which I thought might somehow be related to the problem, however to test it out, I copied and pasted the data shown there as values, and still got the same error.

=FILTER(INDEX(table_rawData,SEQUENCE(ROWS(table_rawData)),XMATCH(A1:BQ1,table_rawData[#Headers])),(table_rawData[BR]="Yes")+((table_rawData[PatronRiskLevel]="POI")*(table_rawData[BR]="No")))

When I click on "debug", it highlights the fourth line of code: Range("A4").Resize(endOfRows, 69).Select . This code works fine on my other sheet, so I just can't figure out why it is not working on this sheet?

 

[Fluff]

Try

endOfRows = Range("A" & Rows.count).End(xlUp).Row

 

[helpneeded2]

Try

endOfRows = Range("A" & Rows.count).End(xlUp).Row

Thank you. This does seem to work well, although there is one oddity - it also selects 3 blank rows under where my data is showing. To make sure there was no "ghost data" I delete all the rows on the sheet even though I knew it was not the issue. Since the data it is copying from is from a FILTER function, the entire range of data is displayed with a blue line around it. This code, copies all the data, plus 3 rows below the blue line. Any idea why it does that?

I'm fine with it copying 3 blanks lines and can use the code now as is, but I was just curious as to why it does this.

 

[Fluff]

You need to use

Range("A4").Resize(endOfRows -3, 69).Select

because you are copying from row 4
However as it's a spill range you can simply use

Sub rangeSelection()
   Range("A4").SpillingToRange.Copy
End Sub

 

[helpneeded2]

Thanks again. That works great now.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[helpneeded2]

Oddly enough, this is no longer working.

Sub CopyData()
    Range("A4").SpillingToRange.Copy
    Selection.Copy
End Sub

The filter for the sheet, is in A4.

When I run the macro, it only copies what ever the current active cell is on the sheet. If I click the A4 cell to make it the active cell, then this macro only copies the data that is in that 1 cell.

 

[Fluff]

You need to remove this line

    Selection.Copy

 

[helpneeded2]

Ah yes. Thanks again. I rarely use VBA so I am not at all familiar with it's syntax.

 

[Fluff]

My pleasure.