Delete Data

howard

Well-known Member
Joined
Jun 26, 2006
Messages
6,601
Office Version
  1. 2021
Platform
  1. Windows
I have the following code below that where text " AC060" appears in Col A, it must delete this row and the next 6 rows including the row containing AC060

However, nothing happens when I activate the code. I have also copied sample data

Your assistance in resolving the problem will be most appreciated

Sub Delete_Unwanted()
Dim LR As Long, i As Long
For i = 6 To LR
LR = Range("A" & Rows.Count).End(xlUp).Row
If Range("A" & i).Value = " AC060" Then Range("A" & i).Resize(6).EntireRow.Delete
Next i
End Sub


Excel Workbook
ABCDE
1A-712536GEN31/10/2010-2500000
2A-712536GEN31/10/2010-500000
3A-712536GEN31/10/2010-250000
4A-712536GEN31/10/2010-1000000
5A-712536GEN31/10/2010-1000000
6A-712536GEN31/10/2010-1000000
7A-712536GEN31/10/2010-500000
8A-712536GEN31/10/2010-1200000
9A-712536GEN31/10/2010-1700000
10A-712536GEN31/10/2010500000
11A-712536GEN31/10/2010500000
12AC060CE
13etailed
14
15Accoun/Dates: 01/Control#s: ALL
16A/C NoRef NoJRNDateAmount
17--------------------------------------
18A-712536GEN31/10/2010500000
19A-712536GEN31/10/20101500000
20A-712536GEN31/10/20101500000
21A-712815GEN31/10/2010300000
22A-712815GEN31/10/2010150000
23A-712815GEN31/10/2010550000
24A-712815GEN31/10/2010-700000
25A-712815GEN31/10/2010-300000
26A-712805GEN31/10/2010750000
27
28
29
30
Sheet1
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Try this

Code:
Sub Delete_Unwanted()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 6 Step -1
    If Range("A" & i).Value = "AC060" Then Range("A" & i).Resize(6).EntireRow.Delete
Next i
End Sub
 
Upvote 0
Hi Peter

Thanks for the help, much appreciated
 
Upvote 0

Forum statistics

Threads
1,224,603
Messages
6,179,853
Members
452,948
Latest member
UsmanAli786

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top