Results 1 to 2 of 2

Default Double Click Clear macro

Threaded 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

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