How to set Target Range?

[u3rick]

I am new and have a problem! I have a sheet called OwnerLog and it lists all the property owners that own rental units. I have a cbo box that I want to populate with those owners and their IdNumbers. I have made a sheet called Menu1 and its used to fill all the different cbo boxes that the program has. I recorded a Macro that works but it’s slow and flashes a lot. Here it is:

Please Login or Register  to view this content.

Now I am trying to write better code and am working on this code:

Please Login or Register  to view this content.

My problem is the target range, that I would like to use would be a named range. But I can’t figure out how to give it a bottom row that matches the source range. Don’t really know if this is the best method either? As written it works but gives NA’s in non- populated source cells in the Menus1 cells range. It needs to be fixed though as owner list will change in size from time to time. Sorry didn’t include workbook as it has live Social Security numbers etc. I am using Excel 2013.

All help is gladly accepted and appreciated!

Thanks

[u3rick]

Sorry the formatting of my post is messed up 1st time posting! Not so good.

[u3rick]

Hi

As a new poster how do I get my code in those nice boxes as others I have seen on the site?

Thanks

[u3rick]

Yes I got it finally! the codebox to look right, not the Answer! I still need help with that!

[u3rick]

Here is a attachment of the file. Hope this helps interest someone in offering help?

[skywriter]

I'm trying to figure out what you want to do here. You have a list that you use as a data source for a combo box and you want to use a macro to update that list?

If that's the case then I would just have the macro paste all the data from the OwnerLog sheet to the Menus1 sheet and onMenus1 sheet make a named dynamic range as your combo box data source. If it is dynamic then it doesn't matter how far the data goes it will fill your combo box with only the cells that have names.

You could also run this update macro anytime data on the OwnerLog sheet changes, that way it is always up to date.

The code below will update your Menus1 sheet.
If you like it you can remove all your old code that I commented out.

Please Login or Register  to view this content.

Put the code below in the code for the OwnerLog sheet. Right click on the OwnerLog tab, choose view code and paste this code into the window.
After you paste the code look at the very top of the window and make sure it says OwnerLog Code to verify it's in the right place.

Please Login or Register  to view this content.

[u3rick]

skywriter

Yes, That's my goal. I am very new to this, and I know I should know the difference, but point me to where I can find what you mean by Dynamic?

Thanks for your help!

u3rick

[skywriter]

See my post #6 above I edited it. There's code to automatically update your owner's menu list anytime you update the OwnerLog sheet.

You have dynamic named ranges in your worksheet. If you go under name manager they are in there. You didn't make those? They are what you can do on the Menus1 sheet. Make the area where the Owner's information is a dynamic named range and use that name as the data source for your combo box.

[u3rick]

skywriter

Thanks so much for your help! I am traveling today and will work on this tonight. If I have questions will post back then.

Thanks again!

u3rick

[skywriter]

To make a dynamic named range for the owner information on the Menus1 sheet you can use the formula below.

Thanks for the rep. points.

Please Login or Register  to view this content.

[u3rick]

Skywriter

That works great! I had one other issue that I had not tackled in my code on this list and that is the owners need to be active. Being active equals Yes in Column D of the OwnerLog. I was going to use a filter but your code is nice and clean and don't know if a filter would work or maybe an If question would be better. Any ideas on how I might fit that in? also how do I show this post as answered?

Thanks again

u3rick

[skywriter]

Try this code.

If your original request is fulfilled please click the Thread Tools drop down box above your first post and choose solved.

Please Login or Register  to view this content.

[u3rick]

Skywriter

That works great. I have only been doing VBA code 2 months and I can follow all the code you gave me but the following. For learnings sake can you explain the >4 part in this code? I know it is most likely simple but not seeing it at the moment.

[/CODE]If lrTarget > 4 Then wsTarget.Range("AE5:AG" & lrTarget).ClearContents[CODE]

Tried to give you another reputation but it won't let me!

Thanks again for all your help!

u3rick

[skywriter]

The code is meant to clear out the old owner information on the Menus1 sheet. If you look at the earlier code for lrTarget you will see how we assign a number to that variable.

Please Login or Register  to view this content.

Rows.Count is a very large number, it's all the rows on a worksheet. End(xlUp) is the same as being below some data and holding the control key and using the up arrow, it will immediately take you to the next cell with data above where you were. Put some data in row 10 of a spreadsheet, go down several hundred rows in the same column, hold down the control key and press the up arrow and you jump right to the cell with data.

So the code goes to the very last cell of the worksheet in column AE and jumps up to the last row in that column with data and assigns the row number to lrTarget.

If there is no data in row 5 or lower it will hit your header row and assign 4 to lrTarget, the clearcontents code will then wipe out your header information. This is basically checking for data. If you or someone else has already erased the data this keeps your header row intact.

As you see the code is written for specific worksheet names, specific columns, rows etc. If you change worksheet names, move data, remove rows etc. you will have to modify the code. Also as you can see if you were to put unrelated data below where you have the owner names the clear contents code would wipe it out.

Sometimes you have people that don't understand this and they won't post their real workbook, they post a sample where all the data is in a different place, sheet names are not correct etc. etc. and then when you write them code based on their sample they don't understand why it won't work in their real workbook. Another instance is they think they can just copy code that you write from one workbook to any other workbook and it should just work.

Good Luck.

[u3rick]

Skywriter

That makes perfect sense now.

Thanks

u3rick