Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Long
Dim LR As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = LR To 2 Step -1
If Range("A" & i) = "Your Text" Then
Range("A" & i).EntireRow.Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub
Sub Macro
Dim i As Long
Dim LR As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For i = LR To 2 Step -1
If Range("A" & i) = "Your Text" Then
Range("A" & i).EntireRow.Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub
Sub DeleteRowsSecondFastest()
''''''''''''''''''''''''''
'Written by www.ozgrid.com
''''''''''''''''''''''''''
Dim rTable As Range
Dim rCol As Range, rCell As Range
Dim lCol As Long
Dim xlCalc As XlCalculation
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = .CurrentRegion
On Error GoTo 0
End If
End With
'Determine if table range is valid
If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction.CountA(rTable) < 2 Then
MsgBox "Could not determine you table range.", vbCritical, "Ozgrid.com"
Exit Sub
End If
'Get the criteria in the form of text or number.
vCriteria = Application.InputBox(Prompt:="Type in the criteria that matching rows should be deleted. " _
& "If the criteria is in a cell, point to the cell with your mouse pointer", _
Title:="CONDITIONAL ROW DELETION CRITERIA", Type:=1 + 2)
'Go no further if they Cancel.
If vCriteria = "False" Then Exit Sub
'Get the relative column number where the criteria should be found
lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _
& "the criteria can be found.", Title:="CONDITIONAL ROW DELETION COLUMN NUMBER", Type:=1)
'Cancelled
If lCol = 0 Then Exit Sub
'Set rCol to the column where criteria should be found
Set rCol = rTable.Columns(lCol)
'Set rCell to the first data cell in rCol
Set rCell = rCol.Cells(2, 1)
'Store current Calculation then switch to manual.
'Turn off events and screen updating
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
'Loop and delete as many times as vCriteria exists in rCol
For lCol = 1 To WorksheetFunction.CountIf(rCol, vCriteria)
Set rCell = rCol.Find(What:=vCriteria, After:=rCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Offset(-1, 0)
rCell.Offset(1, 0).EntireRow.Delete
Next lCol
With Application
.Calculation = xlCalc
.EnableEvents = True
.ScreenUpdating = True
End With
On Error GoTo 0
End Sub
Sub DeleteTableRow()
YourValue = "08"
Range("F1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = YourValue Then
R = ActiveCell.Row - 1
Selection.ListObject.ListRows(R).Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
20 Experts available now in Live!