+ Reply to Thread
Results 1 to 2 of 2

Default Double Click Clear macro

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-19-2007
    Location
    Poole England
    MS-Off Ver
    2010
    Posts
    117

    Default Double Click Clear macro

    Hello All,
    I'm just trying to get an old clear data macro to work again,, & I just can't seem to see why it isn't working :-(

    for cross posting purposes I have also posted here;
    http://www.mrexcel.com/forum/showthread.php?t=614748

    A copy of a test spreadsheet I've uploaded here; (excel 2010 macro enabled sheet)
    http://dl.dropbox.com/u/16052166/Dou...ick_Macro.xlsm

    The code I'm having trouble with is;
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
        Dim RowsToClear     As String
        Dim x, i As Long, y
        Dim AllRowsAddress  As String
        Dim RowAdjust       As Long
        Const RowsToExclude As String = "{1,2,3,19,20,36,37,54}"
        AllRowsAddress = "B5:B44" '<<=== adjust to suit
        
        RowsToClear = Application.InputBox("Enter the rows separated by comma", "Clear Rows", "1,7,9", Type:=1 + 2)
        
        If RowsToClear = "False" Then Exit Sub
        
        RowAdjust = Range(AllRowsAddress).Row - 1
        
        If MsgBox("Are You Sure You Want To Delete These ROWs " & vbLf & RowsToClear & " ?", vbYesNo + vbInformation) = vbYes Then
            x = Split(RowsToClear, ",")
            On Error Resume Next
            For i = 0 To UBound(x)
                y = Evaluate("=match(" & CLng(x(i)) & "," & AllRowsAddress & ",0)")
                If Not IsError(y) Then
                    Application.Intersect(Range("c:q"), Rows(x(i) + RowAdjust)).ClearContents
                End If
            Next
            On Error GoTo 0
        End If
        
    End Sub
    I just can't see why it isn't working in the test sheet??
    If somebody can advise me what's wrong/needs changing etc it would be a great help.

    Basically I would just like it to work clearing rows of data,, C5:Q5
    when you double click a pop up appears,,,
    & you can manually enter the row numbers (Row numbers by the numbers in Column B (B5:B44),, not the cell row numbers)

    If anybody understands VB here, please,,
    It's above me.

    Many thanks for all your time
    I hope somebody can help me fix it.
    Best Regards
    TheGhost
    Last edited by theghost; 02-19-2012 at 11:30 AM. Reason: tipo

  2. #2
    Forum Contributor
    Join Date
    02-19-2007
    Location
    Poole England
    MS-Off Ver
    2010
    Posts
    117

    Re: Default Double Click Clear macro

    Not even sure what I've done??
    I've got it working,,?????

    It must be how I've put the formula into VB?

    Hey,, at least it's working now,,
    I'll mark this as solved.

    Many thanks
    TheGhost.

    (Still not sure what I've done,,but hey, it's working :-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1