VBA select cell range based upon the # of rows that make up a certain merged cell?

**clemsoncooz** · 12-30-2011, 05:21 PM

This is slightly confusioning.

Are you able to specify the certain cell range that you want selected based upon how many rows make up a differnet certain merged cell?

I ask, because my range of date changes depending upon how many rows the user deletes or inserts. The one constant that I can think of to reference my automatic selection back to is, the number of rows within the merged cell that is adjacent to my data range that I want selected.

If you look at the attached. File - Sample12-30.xlsm

I know there are different ways to select this data, but my goal here is to select data in Column H (only the dates that make up each phase). For instances, at different points in my custom macro, I want to select only the months that make up "Phase 2" and at other times I want to select the months in "Phase 1" and also "Phase 3."

My current solution is to name the range (for instance in my sample doc, name H7:H12 - "months_in_phase1" and just call it out when needed.

Please Login or Register  to view this content.

But my problem is that, that range of data is dependent upon how many rows the user inserts or deletes. If the user deletes the last row of the name range in one instance and inserts another (which is possible) - then that name range quite isn't what it needs to be.

THEREFORE, my only solution is to think that I need to copy only the number of rows (in Column H) that are contained in the merged cell titled 'PHASE #' - e.g so if "PHASE 2" contains 10 merged cells, then only copy 10 rows in Column H. So my question is, can that be done and how?

THANK YOU SO MUCH FOR LOOKING AT THIS WITH ME.

**dangelor** · 12-30-2011, 05:52 PM

I suggest you unmerge the cells and repeat the Phase # in each row, then use Auto Filter to display your choice of Phases.

**watersev** · 12-30-2011, 06:20 PM

hi clemsoncooz, hope this helps, press Test, the code will show months address for each phase

**clemsoncooz** · 01-03-2012, 01:05 PM

Originally Posted by watersev

hi clemsoncooz, hope this helps, press Test, the code will show months address for each phase

Wonderful! With my limited knowledge of VBA and after selecting and deleting rows (espically the last one within that merged cell) - this appears to be working just the way I need it to - SO THANK YOU!

But one little change, how can I then have what is displaed in the MsgBox, be selected. So I can then have my current Macro do what I have it programmed to do?

**watersev** · 01-03-2012, 03:25 PM

commented code:

Please Login or Register  to view this content.

PS. Never use select in your code. Select is used above for visualizing the result range.

**clemsoncooz** · 01-03-2012, 04:10 PM

Thanks watersev. That helps!

I revised your code a little to fit exactly what I was doing. I dont need the loop as Phase 1 is a specific name = DESIGN PHASE and Phase 2 and Phase 3 are something else. I modified the code to figure that out. This is what I have.

Please Login or Register  to view this content.

This seems to work, but I have one problem, this "table" of data is on a long worksheet of which the terms "DESIGN PHASE" occur mutiple times. Is there a way to set this search within a certain range - e.g. set the range of the search_result to only occur in certain areas?

**watersev** · 01-03-2012, 05:07 PM

Re: your revised code
1. If you do not need loop, I do not mind. The original code was designed basing on the sample provided.
2. This line:

Please Login or Register  to view this content.

might cause you a trouble as soon as you use Ctrl+F and change search parameters. If you take a look at my "Find" line of code it has parameters explicitly set to avoid such problem.

Yes, you can define ranges in the code as soon as they are fixed. To get a more detailed answer please post sample workbook with explanations attached.

**clemsoncooz** · 01-03-2012, 05:55 PM

ok thanks for the heads up. I think that was what I was describing. If you look at the sample workbook attached, sample1-4-12.xlsm

This table will appear anywhere within Column B but will always be between the COMPENSATION heading and MODIFICATION heading (but those values will not always be in A4, and A55 respectively. Is there a way to set the range of the search (looking for DESIGN PHASE) to be within those two headings?

**watersev** · 01-03-2012, 06:40 PM

please check attachment, run code "test"

Please Login or Register  to view this content.

**clemsoncooz** · 01-04-2012, 12:22 PM

This, watersev, I do believe is perfect (so far) and exactly what I needed! Thank you so much!

VBA select cell range based upon the # of rows that make up a certain merged cell?

LinkBack

Thread Tools

Rate This Thread

Display

VBA select cell range based upon the # of rows that make up a certain merged cell?

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Re: VBA select cell range based upon the # of rows that make up a certain merged cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions