select range based on a cell value

**modytrane** · 09-18-2008, 09:51 AM

I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet.
So far I have everything working fine.
Now I want to create a button to trigger a macro to sort that block.
I can figure out the button and make it trigger a macro.
One of the lines in the macro is

Please Login or Register  to view this content.

How do I make it so this line looks at cells AA3 and AB3 to find the range instead of A3 and J23 ?

I don't know the syntax in VBA.

Thanks for your help.
modytrane

**Andy Pope** · 09-18-2008, 09:57 AM

Try this, although usually selection of a range is not required to perform further coding against.

Please Login or Register  to view this content.

**modytrane** · 09-18-2008, 10:06 AM

Didn't work.
I got an error msg:

Run time eoor 1004
Method 'Range' of object '_Global' failed.

modytrane

**BigBas** · 09-18-2008, 10:20 AM

Why not try an approach like the one below. It determines the last row and the last column in the used range, and sorts accordingly.

Report back

Please Login or Register  to view this content.

**Andy Pope** · 09-18-2008, 10:21 AM

works for me.

**modytrane** · 09-18-2008, 10:34 AM

Thanks for your reply.

However my situation is a little different.
I actually have three blocks of data on one sheet.
Each block will vary in size [upper left cell and lower right cell].
I am calcualting these corner cells based on titles of each block.
These corner cell values are stored in a fixed area of the worksheet.

I wanted a way for VBA to read those cell values and insert them in the
range (.........).Select statement.

Hope this makes it clear.
Thanks for your help,
modytrane

**Andy Pope** · 09-18-2008, 10:46 AM

rather than make us exhaust our psychic powers post an example of your data and explain your problem rather than a subset of it.

**modytrane** · 09-18-2008, 11:54 AM

Ok here's the example file.

Cells AJ3:AK5 store the range values.
As you can see the three blocks under John, Andy and Bill will change in size as accounts are added or deleted. Width will remain same.

I have Macro1 created with your suggested code.

I know this will be easy for you.

Thanks,
modytrane

**Andy Pope** · 09-18-2008, 02:48 PM

The cells you gave as the source of information was incorrect.
So not AA3 and AB3 but AJ3 and AK3.

Please Login or Register  to view this content.

Your sort is done on J3 which is not going to be applicable to all section.
So try changing the key to

Please Login or Register  to view this content.

although only the first bank of data has information in column J

**modytrane** · 09-18-2008, 04:25 PM

Thanks Andy for your esponse and time.

I never got your formula to work, even with correct reference info.
I did pick up some clues from posts by you and BigBas and used this code which does the sorting for all three blocks.

Please Login or Register  to view this content.

**Andy Pope** · 09-19-2008, 04:31 AM

I added a random number to column J to give the data something to sort on.
This worked for me.

Please Login or Register  to view this content.

select range based on a cell value

LinkBack

Thread Tools

Rate This Thread

Display

select range based on a cell value

select range based on cell value

select range based on cell value

select range based on cell value

select range based on cell value

Thread Information

Users Browsing this Thread

Similar Threads

count cells in a range based on another cell

Getting "Method 'Range' of Object '_Global Failed" with Range...Select

Conditional Format a cell based on a range of numbers

Change cell background based on another cell

Code to select a range of cells based on cursor location

Bookmarks

Bookmarks

Posting Permissions