Combox Coding Issue

jbottlin

New Member
Joined
Sep 23, 2014
Messages
2
I have written the following code for a combox in excel. The purpose is to delete the entire row when the combox selection is 'No' if a cell in a specific column has certain information. The problem is that it deletes the rows when 'No' and 'Yes' are selected. I know I am missing something trivial but I can't seem to see it. Can someone provide some guidance; it would be greatly appreciated.Private Sub ComboBox1_Change()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
Select Case ComboBox1.Value
Case "No"
End Select
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'Change to Benchmark Sheet
With Sheets("Assessment Benchmarks")
'Select the sheet to change the window view
.Select
'Go back to normal view, for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'Loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'Check the values in the AA column
With .Cells(Lrow, "AA")
If Not IsError(ComboBox1.Value) Then
If .Value = "abcd1234" Then .EntireRow.Delete
'Delete each row with the Value "abcd1234" in Column AA, case sensitive.
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Watch MrExcel Video

Forum statistics

Threads
1,109,330
Messages
5,528,043
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top