• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

VBA Delete DropDowns in cells

Hi. I am using code like that shown below to add cells to a hundred cells in the top
row of a spreadsheet. What code would I use to delete all DropDowns in the top row of the sheet

Sub DD()

With ActiveSheet.DropDowns.Add(142.5, 38.25, 108.75, 24)
    .Top = Range("C1").Top
    .Left = Range("C1").Left
    .Height = Range("C1").Height
    .Width = Range("C1").Width
End With


End Sub
0
Murray Brown
Asked:
Murray Brown
1 Solution
 
dlmilleCommented:
Attached are two subs.  One creates dropdowns from A1:G5, so for 5 rows we have drop downs.  The second sub deletes only those dropdowns on the first row (again, looking from A:G, but you can change the range).

It does this by checking the rows .Top and .Left and compares this with the dropdowns that are parked in the same location's .Top and .Left.

Sub DD()
Dim r As Range
Dim rng As Range

    Set rng = Range("A1:G5")
    For Each r In rng
        With ActiveSheet.DropDowns.Add(142.5, 38.25, 108.75, 24)
            .Top = r.Top
            .Left = r.Left
            .Height = r.Height
            .Width = r.Width
        End With
    Next r

End Sub

Sub deleteDropDownsFirstRow()
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim sObj As Object

    Set wks = ActiveSheet
    Set rng = wks.Range("A1:G1")
    
    For Each sObj In wks.Shapes
        If sObj.FormControlType = xlDropDown Then 'Found a drop down
            'check if on row 1 using .Top & .Left of each cell being tested
            For Each r In rng
                If sObj.Top = r.Top And sObj.Left = r.Left Then
                    sObj.Delete
                    Exit For
                End If
            Next r
        End If
        
    Next sObj
End Sub

Open in new window


See attached demonstration workbook.

Cheers,

Dave
delDropDownsRow1-r1.xls
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thanks very much
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.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now