CODEWITHSUNDEEP
excelrow-removalvba
Alex
Alex

Reputation: 184

Delete a row with pictures?

I have a workbook and have a few rows that have pictures in them. When I delete the whole row the picture stays behind it.

What I need to do is find vba code that will let me delete the selected row and the pictures as well.

How I can do this?

Upvotes: 0

Views: 2166

Answers (1)

Joseph
Joseph

Reputation: 5160

Here's something to get you started:

Option Explicit

Public Sub deletePics()
    Dim pic As Shape
    For Each pic In ActiveSheet.Shapes
        If (pic.Type = msoPicture) Then
            Debug.Print pic.TopLeftCell.Address
        End If
    Next pic
End Sub

You can loop through all the Pictures, and collect their addresses (or rows only) and store it in an array. When you loop through your delete code, you can pass the row and the array to a function that will check to see if there is a pic in that row, and if there is, delete it (using pic.Delete).

EDIT:

Since this is more complex than usual, and you're new to VBA, here's a more concrete sample:

Option Explicit

Public Function deleteCells()
    Dim lastRow As Long
    Dim ws As Worksheet
    Dim rowHasPic() As Shape

    Set ws = ActiveSheet
    rowHasPic = getPicData()

    ' get last row
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    ' loop through cells from bottom to top, deleting rows that contain "Delete" in column a
    ' and delete the pic as well
    For i = lastRow To 1 Step -1
        If (ws.Cells(i, 1).Value = "delete") Then
            ' delete pic first, if available
            If (Not rowHasPic(i) Is Nothing) Then rowHasPic(i).Delete

            ws.Cells(i, 1).EntireRow.Delete
        End If

    Next i
End Function

Public Function getPicData() As Shape()
    Dim ws As Worksheet
    Dim pic As Shape
    Dim a() As Shape
    Dim lastRow As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

    ' set the boundaries as if they are rows in a worksheet
    ' this is so we can easily check if a row has a pic later in the code
    ReDim a(1 To lastRow)

    ' loop through the shapes, only grab pictures and set their row in an array
    For Each pic In ActiveSheet.Shapes
        If (pic.Type = msoPicture) Then
            ' set the particular "row" of the array to true to know if you
            ' have an image in this row
            Set a(pic.TopLeftCell.Row) = pic
        End If
    Next pic

    getPicData = a
End Function

To summarize, I create an array of Shapes such that the array indexes match how many rows are on the worksheet. As I loop through all the shapes in the worksheet, I check if it's a pic, and if it is, then set it to the array at that particular row.

Then, I loop through the worksheet from bottom to top, and check for "delete" in column A in order to determine if I should delete it.

If I find that in column A there is "delete" in the text, then I check for the image in the array and if it's there, then delete it. Then I delete the whole row.

NOTE

If you have multiple pics in the same row, this will not work for you. You would have to code it differently.

Upvotes: 1

Related Questions