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
Bookmarks