Find and delete data based on header row values...

[dpmicka]

I have a sheet with a header row (A1:D4 = Blue, Yellow, Green, Red). The values in A2:D1000 might be any single digit number 0-9. Is it possible to set up a macro or some code that will present the user with two input boxes in succession (one for header row value, next for digit value), then delete all values that meet both criterias. For example, if user entered 'Red' in first input box and '3' in second box, code would search for any cells that contained a value of 3 in column D and delete those cells (shifting other cells in D up). Any ideas???

 

[dpmicka]

That does make the 'exit sub' piece work on the 2nd input box, but the 'clear contents' piece (which is the main idea of the code) doesn't work later on in the routine with Dim xVal as Variant (change Variant back to Double and 'clear content' works but not 'exit sub'). It's enough to drive you crazy!

 

[kkknie]

Try this...

Sub test()

Dim xCol            As Variant
Dim xVal            As Double
Dim strVal          As String
Dim c               As Object
Dim LastRow         As Long
Dim x               As Long

xCol = InputBox("What Color??")
If Len(xCol) = 0 Then Exit Sub
strVal = InputBox("What value??")

'Check if blank
If Trim(strVal) = "" Then Exit Sub
'Make sure it is a number
If Not IsNumeric(strVal) Then Exit Sub
xVal = CDbl(strVal)

    With Sheets("Sheet1").Range("A1:D1")
        Set c = .Find(What:=xCol)
    End With
    If c Is Nothing Then Exit Sub
    
    xCol = c.Column
    LastRow = Cells(65536, xCol).End(xlUp).Row
    For x = 1 To LastRow
        If Cells(x, xCol).Value = xVal Then
            Cells(x, xCol).ClearContents
            Else
        End If
    Next x

End Sub

It reads the number in as a string, checks for nothing, checks for not a number, converts to a number and seems to work.

K

 

[dpmicka]

That works great K - thanks! One other thing I was wondering about - if the numeric value in the 2nd inputbox is not found, is there a way to flash a msgbox before exiting sub saying ("number not found in" c) where c is the color entered into the 1st inputbox?

 

[kkknie]

Change

If c Is Nothing Then Exit Sub

To

If c Is Nothing Then 
   Msgbox "Color " & xCol & " not found."
   Exit Sub
End If

It won't flash, they will have to click OK to get it to go away.

K

 

[dpmicka]

That works for xCol (color), but not xVal (the numeric value). The scenario I'm wondering about is when the user enters a valid color, then enters a "not found" number within that color's column. It's tricky...

 

[kkknie]

Duck soup!

Sub test() 

Dim xCol            As Variant 
Dim xVal            As Double 
Dim strVal          As String 
Dim c               As Object 
Dim LastRow         As Long 
Dim x               As Long 
Dim bFound      As Boolean

xCol = InputBox("What Color??") 
If Len(xCol) = 0 Then Exit Sub 
strVal = InputBox("What value??") 

'Check if blank 
If Trim(strVal) = "" Then Exit Sub 
'Make sure it is a number 
If Not IsNumeric(strVal) Then Exit Sub 
xVal = CDbl(strVal) 

    With Sheets("Sheet1").Range("A1:D1") 
        Set c = .Find(What:=xCol) 
    End With 
    If c Is Nothing Then 
       Msgbox "Color " & xCol & " not found." 
       Exit Sub 
    End If     
    xCol = c.Column 
    LastRow = Cells(65536, xCol).End(xlUp).Row 
    bFound = False
    For x = 1 To LastRow 
        If Cells(x, xCol).Value = xVal Then 
            Cells(x, xCol).ClearContents 
            bFound = True
        End If 
    Next x 

    If bFound = False then Msgbox "Number " & xVal & " not found!"
End Sub

 

[dpmicka]

BINGO!!! Thanks!!!