User inserted value

[J.W. Aldridge]

Code works, but I need to alter the filter to replace the code portion
"ABC", with a popup box that asks the user what is the name of their
group, so that all other rows are deleted instead of having it in the
code.


Sub DeleteRows()

Dim LastRow As Long
Dim rw As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
For rw = LastRow To 2 Step -1
If Cells(rw, "c").Value <> "ABC" Then
Rows(rw).Delete Shift:=xlUp
End If
Next rw
Application.ScreenUpdating = True
End Sub

 

[Don Guillett Excel MVP]

Code works, but I need to alter the filter to replace the code portion
"ABC", with a popup box that asks the user what is the name of their
group, so that all other rows are deleted instead of having it in the
code.

Sub DeleteRows()

Dim LastRow As Long
Dim rw As Long
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, "c").End(xlUp).Row
    For rw = LastRow To 2 Step -1
        If Cells(rw, "c").Value <> "ABC" Then
            Rows(rw).Delete Shift:=xlUp
        End If
    Next rw
    Application.ScreenUpdating = True
End Sub

Look in the vba help index for INPUTBOX

 

[J.W. Aldridge]

Ok... Trying to put this all together but not quite sure where I'm
losing it.
Created the line "x=inputbox..."
Also changed the reference to x, however it doesn't quite work. I cant
even cancel out of the message box.
Any ideas..., help..., please?

Sub DeleteRows()


Dim LastRow As Long
Dim rw As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
For rw = LastRow To 2 Step -1

x = InputBox("Type three letter dept code please", "Department
Filter")

If Cells(rw, "c").Value <> x Then
Rows(rw).Delete Shift:=xlUp
End If
Next rw
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

You only want to ask the user for the deptcode to keep a single time. So you
don't want to put it a loop.

Maybe this will be closer:

Option Explicit
Sub DeleteRows()

Dim LastRow As Long
Dim rw As Long
Dim DeptCode As String

DeptCode = InputBox(Prompt:="Please type three letter dept code to keep", _
Title:="Department Filter")

If Len(Trim(DeptCode)) <> 3 Then
MsgBox "That's not 3 characters!"
Exit Sub
End If

Application.ScreenUpdating = False
With ActiveSheet 'I like to qualify my ranges
LastRow = .Cells(.Rows.Count, "c").End(xlUp).Row
For rw = LastRow To 2 Step -1
If LCase(.Cells(rw, "c").Value) <> LCase(DeptCode) Then
.Rows(rw).Delete
End If
Next rw
End With
Application.ScreenUpdating = True

End Sub

 

[J.W. Aldridge]

purrrrfecto!

Thanx much.

 

[Bob Phillips]

This should work

Sub DeleteRows()


Dim LastRow As Long
Dim rw As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "c").End(xlUp).Row
For rw = LastRow To 2 Step -1

x = InputBox("Type three letter dept code please", "Department
Filter")

If Cells(rw, "c").Value <> x Then
Rows(rw).Delete Shift:=xlUp
End If
Next rw
Application.ScreenUpdating = True
End Sub