Log In
Register
Home
Excel Expert
Power Query
July 14, 2022
Offline
Hi everything
Please could someone point be in the right direction - using Excel 365.
Want to detect the last row of data (I know the last column) and then create a named range based on my known column and the last row that I detected?
Any ideas please?
Cheers all
July 16, 2010
Offline
Hi Rod,
You don't need VBA to do this. You can use a dynamic named range. You can use INDEX and MATCH to find the last value in a column and define it as a dynamic named range.
If you get stuck come back with a sample Excel file so we can give you a specific solution.
Mynda
Excel Expert
Power Query
July 14, 2022
Offline
Thanks Mynda, I'll give it a try.
My situation is that I'm combining data from some 20 external Excel worksbooks into a single sheet using Paste Link, then sorting the white space, then I have a load of pivot charts I want to create based on a dynamic named range. My external sheets are all fixed size (so maybe 100 rows and 20 columns) but not all full of data, so I run a short sort macro to move everything to the top in the new sheet (does that make sense?). I'm keeping the "shell" of my destination sheet read-only, so that I can repear the process as the data in my source sheets change.
I've discovered that I can't paste link into a table, and paste link doesn't copy formatting. So I need a way of dymanically creating a new name range (or table) based on my newly sorted data, so that the pivot charts that are in the destination sheet can refresh and update a dashboard.
I didn't realise that you can have a formula in range manager - and that helps a lot!!!
Really appreciate you getting back to me
Rod
Excel Expert
Power Query
July 14, 2022
Offline
Thanks Mynda
Been looking at Power Query for a few hours - what a revelation!!! Thanks 🙂
July 16, 2010
Offline
Glad you like the look of Power Query, Rod. It's a game changer. If you'd like to learn more, please consider my Power Query course.
1 Guest(s)
