asked on

Using VBA for excel how can I find the last shape (type 12) on a worksheet to select the corresponding number next to it.

Hi Experts,
I have a set of shapes on a worksheet.
They are going down column A.
Next to each shape is a corresponding number eg going down column C.
So every number has a shape next to it.
I would like to be able to select the last shape which is half way down my record of data. By doing that I would know which row the corresponding number is.
I can then delete the rest of the numbers that continue going down the list because they don't have a matching shape.

So basically all I need is the set of numbers that have a shape (being type 12) next to it. I could then just get rid of the numbers down the column that don't have the shapes next to it in the worksheet.

patrickab

tolgss,

As there will be no linkage between the numbers in column A and the shapes I do not believe you can use those numbers as the guide for which shapes to delete. My I suggest that you upload your file so we can see the challenge. You will find free file upload services at these sites:

http://storenow.net/
http://www.rapidupload.com/
http://rapidshare.de
http://briefcase.yahoo.com
http://geocities.yahoo.com
http://yousendit.com
http://www.megafileupload.com/
http://www.ee-stuff.com

Important: Remember to copy the link before you leave their webpage and then quote it here.

Or

If you upload the workbook to www.ee-stuff.com you will need to do the following:

1. Zip the file
2. Login with the same details as for Experts Exchange
3. Select the Expert tab, and choose the Upload a new file link
4. Enter the question number. From a URL extract question number. For example if the URL is:

experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_22753995.html

then enter the question number as - 22753995
5. Browse to your *zipped* file, enter a comment of some sort (it doesn't matter what) and press Upload
6. You will see a page with two links to the file - copy the second one and post it here. If there are no links, then the file failed to upload

p.s. If you don't have an easy-to-use 'zipping' tool you might like to download the BigSpeedZipper. You can get it free from:

http://bigspeed.net/index.php?page=bszipper

Patrick

tolgss

ASKER

Hi and thanks for the reply.
I uploaded the file to rapidshare.de (569 KB - unzipped) and the link is:
http://rapidshare.de/files/37956281/WkshtwithShapes.xls.html

The only aim is to be able to get rid of the set of numbers that don't have a shape, (to be precise its a checkbox wich you would select and then click on a button to view an image for it).

Thanks in advance.

imitchie

There is a way to return a cell reference for a shape. I'm checking it out.

ASKER CERTIFIED SOLUTION

imitchie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tolgss

ASKER

I'll give it a try when I get home..

tolgss

ASKER

Thanks, your code gave me an idea and with a bit of changes to it, it got me to the results I needed. Below is the code I used to make it fully work.

Sub FindLastShapeInColumnA()
Dim sh As Worksheet
Dim s As Shape
Dim lastShape As Shape
Set lastShape = Nothing
For Each s In ActiveSheet.Shapes
If lastShape Is Nothing Then
Set lastShape = s
ElseIf s.TopLeftCell.Row > lastShape.BottomRightCell.Row Then
Set lastShape = s
End If
Next
ActiveWorkbook.Names.Add Name:="NoImage", RefersToR1C1:="=PageWithShapes!R2C15"
Range("NoImage").Value = lastShape.BottomRightCell.Row

Set sh = Worksheets("Pagewithshapes")
sh.Cells(Range("NoImage").Value, 4).Select
End Sub

Thanks to the experts involved.