Find LastRow (based off a specific column) in a Named range and select those cells

I have a meal planner book I'm working where I have 4 separate named ranges each with 9 rows. Each of these (breakfast, lunch, dinner, snacks) are defined in name manager. Not all 9 rows will be used for each day/meal. What I want to be able to do is to select only the rows in each of these named ranges where column C contains data. I have used LastRow in several other workbooks, however all columns in those other ranges all contained data, this is not the case for this sheet. See below for sample breakfast range and code I am trying.

BK.PNG

Please Login or Register  to view this content.

Hi,

it could be so easy if you use Excel tables or named ranges but it seems you missed the top page yellow banner …

Sorry folks, and yes I did miss that banner! I have attached the workbook now. Thanks

I have a sub above the problem sub which clears some other named ranges without issue. The BOLD line below is where I am getting the error "Method 'Range' of object_Global" failed. Therein lies my headache.

Please Login or Register  to view this content.

Not sure why the module did not come over, I uploaded the .xlsm file. i will try to attach just the vb module this time. As for the rest of the code in the sub, it is all commented out, the only thing really missing is the End Sub. I copied this sub over from another workbook where I have similar code running, so the commented code does not pertain to the issue I am having, the entire sub block is:

Please Login or Register  to view this content.

That's very odd, because in my book those named ranges are there, image attached, and I will attach the workbook again, no clue why it did not upload a complete file!

Attachment 682750

Thank you. I used the Insert Image button on the toolbar for the post, didn't know that was invalid, it's been a while since I posted out here.

Thank you Mike, when I used the code below, I got the error "Unable to get the SpecialCells property of the Range class

Please Login or Register  to view this content.

I gave that a go and was presented another error, I posted to @mikerickson post with the error. I am not worried about the clear contents procedure, that works fine. My problem is that I want to copy only the rows in each named range that has data in C over to the Historical Log sheet. If each meal was on it's own worksheet this would not be an issue, however I did not want 4 different sheets for each meal.

I see Mike posted something similar to what I have been working on. The problem with his code may be sheet references and what sheet is active when you run the code. Anyway, here is what I came up with that works on your sample... it copies all the named ranges (Breakfast, Lunch, Dinner and Snacks) all at once, one under the other. Note, though, while I am only copying Columns A:O from your "Daily Log" sheet, Column P on the "Historical Log" sheet is inheriting the "Week Ending" date from the rows above because of the table structure, so you will have to add a line of code to correct that date after you run the one-liner in my macro.

Please Login or Register  to view this content.

Rick, that works great, thank you. One question, is there a way to paste values only using this method? This is bringing over the grid borders with it. If not, then I can adjust the History table to include grid lines as well.

Thanks so much.

Keebellah, thank you, and yes, this is working as well, with only 1 caveat, it is not grabbing the first row in the range when copying.

Originally Posted by lday75

One question, is there a way to paste values only using this method? This is bringing over the grid borders with it.

The easiest way to handle this is simply remove all borders in the UsedRange...

Please Login or Register  to view this content.

Received "wrong number of arguments" error when adding the -1 to

Please Login or Register  to view this content.

Did you get a chance to try the new code I posted in Message #25 yet?

Hi Rick,

This is PERFECT, thank you. I have decided to use your solution as the code is much cleaner and works exactly how I was expecting, and you turned me onto the Intersect function, which I have not used before. GREAT. My new code block is now:

Please Login or Register  to view this content.

Keebellah,

Thank you for all your assistance today, I do appreciate it; however I have decided to use Rick's suggestion as it works just as I was expecting. Again, thank you.