ChristinaAC
New Member
- Joined
- Jul 7, 2015
- Messages
- 10
Hi there,
I have the below code of which I am trying to use on a large amount of data. The column that the search needs to look in is Column A, starting from cell A2. The text that it needs to find is "670164661 - 00001" and "10000011823", if it finds this content in any of the rows in column A, then it should delete the entire row.
Unfortunately there is an error with my code and but I'm not sure why, it keeps highlighting the second to last "Ends with", to say that its not needed.
Please let me know if you can help.
Thank you so much
Sub Findanddeleterows()
Dim FirstRow As Long
Dim LastRow As Long
Dim Looprow As Long
Dim CalcMode As Long
Dim ViewMode As Long
'creating the name for first and last row etc
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
'selecting active sheet to use formula on
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
'if you are in page break/page layout view to normal view and turns off display page breaks
FirstRow = 2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'setting parameters for first row as cell 2 and for the formula to find the last row - this will enable the data level to continue to grow and for the formula to still work
For Looprow = LastRow To FirstRow Step -1
'Starting the find and delete on the last to first row less the heading
With Cells(Looprow, "A")
If Not IsError(.Value) Then
If .Value = "67016997 - 1" Then .EntireRow.Delete
If .Value = "10000011823" Then .EntireRow.Delete
End With
Next Looprow
End With
End Sub
I have the below code of which I am trying to use on a large amount of data. The column that the search needs to look in is Column A, starting from cell A2. The text that it needs to find is "670164661 - 00001" and "10000011823", if it finds this content in any of the rows in column A, then it should delete the entire row.
Unfortunately there is an error with my code and but I'm not sure why, it keeps highlighting the second to last "Ends with", to say that its not needed.
Please let me know if you can help.
Thank you so much
Sub Findanddeleterows()
Dim FirstRow As Long
Dim LastRow As Long
Dim Looprow As Long
Dim CalcMode As Long
Dim ViewMode As Long
'creating the name for first and last row etc
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
'selecting active sheet to use formula on
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
'if you are in page break/page layout view to normal view and turns off display page breaks
FirstRow = 2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'setting parameters for first row as cell 2 and for the formula to find the last row - this will enable the data level to continue to grow and for the formula to still work
For Looprow = LastRow To FirstRow Step -1
'Starting the find and delete on the last to first row less the heading
With Cells(Looprow, "A")
If Not IsError(.Value) Then
If .Value = "67016997 - 1" Then .EntireRow.Delete
If .Value = "10000011823" Then .EntireRow.Delete
End With
Next Looprow
End With
End Sub