Get the last row of a spilled range

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
1,229
Office Version
  1. 365
Platform
  1. Windows
I have a spilled range, which is generated from a Filter Function. I would like to get the last row of that range using VBA. I have used Sheet7.Range("K3").End(xlDown).Row, but it only brings back the the top row where the formula is.

Thanks in Advance
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
That should work though, do you have an example?
 
Upvote 0
Something like:

Code:
with Sheet7.Range("K3").spillingtorange
    msgbox .row + .rows.count - 1
end with
 
Upvote 0
Something like:

Code:
with Sheet7.Range("K3").spillingtorange
    msgbox .row + .rows.count - 1
end with
Hi @RoryA
Sorry I did not make myslef clear in my orignal post, I wanted the row count in a variable. So I got this
'Get Range of Activity Table
ARow = Sheet7.Range("K3").SpillingToRange.Row and it comes up with
1703940756305.png
 
Upvote 0
If the formula is not currently spilling to other cells, you will get that error. You can test for that first:

Code:
With sheet7.Range("K3")
if .hasspill then
with .spillingtorange
arow = .row + .rows.count - 1
end with
else
arow = 3
end if
 
Upvote 0
Solution

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top