Check for Picture in a cell

ssgmiami

New Member
Joined
Nov 12, 2007
Messages
22
Each month I receive a spreadsheet from another office. From it I extract certain data. I have created a macro to loop thru and do this. The problem is, some cells contain both a picture and text. I need to ignore those cells.

I need a way to determine if a cell contains a picture. The only solution I can find on the web is more technical than my VB skills. If I can determine the cell has a picture, I can ignore it in my loop.

An alternative would be to just globally delete the contents all cells containing pictures prior to my loop. It is not a problem if I extract empty cells, just a problem if they contain a picture.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the Picture's TopLeftCell property like this:

Code:
Sub Test()
    Dim Cell As Range
    Dim Pic As Picture
    For Each Cell In Selection
        For Each Pic In ActiveSheet.Pictures
            If Pic.TopLeftCell.Address = Cell.Address Then
                MsgBox "Cell " & Cell.Address(False, False) & " contains a picture"
            Else
                MsgBox Cell.Value
            End If
        Next Pic
    Next Cell
End Sub
 

ssgmiami

New Member
Joined
Nov 12, 2007
Messages
22
Andrew,

Thanks for the quick suggestion. I thought I could figure this out with a little boost. That doesn't seem to be the case. I would like to send a more fully detailed question along with a sample file. Can I do that, if so how?

John
 

ssgmiami

New Member
Joined
Nov 12, 2007
Messages
22
OK, Here goes (this would be far simpler if I sent an actual sample)

The spread sheet I receive has a varying number of columns. Some columns represent sumarrized totals, I do not need those columns. Other colums contain raw data, I need those columns.

For Example:

All Regions Region 1 Peru Colombia Region 2 Belize Guatemala
300 200 125 75 100 50 50

In the small matrix above, the columns titled "All Regions", "Region 1" and "Region 2" contain summarized totals. I do not need them. I need the columns titled "Peru", "Colombia", "Belize" and Guatemala". They represent the raw data.

Each of the summarized colums has a small graphical arrow in the top left of the header cell denoting that the column contains summarized data. I need a way to identify the presence of this arrow and delete the column.

That way, when I am finished I will end up with a spreadsheet that looks like this:

Peru Colombia Belize Guatemala
125 75 50 50

As I said, it is a little more complicated than I originally stated. Does this make any sense? Can it be done?

Thanks,
John
 

Forum statistics

Threads
1,082,551
Messages
5,366,268
Members
400,881
Latest member
DevelopedUnkown

Some videos you may like

This Week's Hot Topics

Top