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.

tolgssAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
Here's some sample code that will get you started. You can turn it into a function, or use it in a larger Sub....

Sub FindLastShapeInColumnA()
    Dim s As Shape
    Dim lastShape As Shape
    Set lastShape = Nothing
    For Each s In Shapes
        If lastShape Is Nothing Then
            lastShape = s
        ElseIf s.TopLeftCell.Row > lastShape.TopLeftCell.Row Then
            lastShape = s
        End If
    Next
End Sub
0
 
patrickabCommented:
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
0
 
tolgssAuthor Commented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
imitchieCommented:
There is a way to return a cell reference for a shape. I'm checking it out.
0
 
tolgssAuthor Commented:
I'll give it a try when I get home..
0
 
tolgssAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.