Macro deletes all data rather than just intended rows

hip2b2

hip2b2

Board Regular
Joined
May 5, 2003
Messages
135
Office Version
  1. 2019
Platform
  1. Windows
The following VBA code is intended to delete the Rows in Sheet2 where Field 12 contains the string ABC (or ABC and anything else), this works as intended. What I have belatedly discovered is that if Field 12 does not contain any instance of ABC all the data in Sheet2 below Row 6 is deleted.

Where have I messed up?

Thanks in advance.

hip

VBA Code: 
Set h = Sheets("For Sheet2")
    If h.AutoFilterMode Then h.AutoFilterMode = False
    lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With h.Range("A6:L" & lr)
    
    .AutoFilter Field:=12, Criteria1:="=ABC" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: ABC
    .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=12
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
What is strName
It might pay to post ALL of the code.
 
Upvote 0
Michael,

You hit the nail on the head; strName is not defined. But now I am way outside my depth, hope you can help

VBA Code: 
Sub TEST()

Application.ScreenUpdating = False
Sheets("Sheet1").Select
Sheets("Sheet1").Range("a:a").RowHeight = 18
Sheets("Sheet1").Range("a:u").Font.ColorIndex = vbBlack
Sheets("For Sheet1").Select
Application.GoTo ActiveSheet.Range("A1"), True
Sheets("Sheet1").Range("a:j,s:s,u:u").Copy Destination:=Sheets("For Sheet2").Range("A1")

'Delete Data Rows "For Sheet2" Sheet
Set h = Sheets("For Sheet2")
If h.AutoFilterMode Then h.AutoFilterMode = False
lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With h.Range("A6:L" & lr)

.AutoFilter Field:=2, Criteria1:="=*" & strName & ".xlsm", Operator:=xlAnd '<Delete Row If Contains: *.xlsm
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=2

.AutoFilter Field:=2, Criteria1:="=ABC" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: ABC
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=2

.AutoFilter Field:=12, Criteria1:="=XYZ" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: XYZ
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=12

.AutoFilter Field:=12, Criteria1:="=ABCXYZ" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: ABCXYZ
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=12

h.AutoFilterMode = False

Application.ScreenUpdating = True
End Sub
 
Upvote 0
In the criteria you have "=*","=ABC","XYZ","=ABCXYZ"
What are you actually trying to delete in real terms !!
Is the "=" sign actually in the cells ??
 
Upvote 0
hip2b2 said:
Michael,

You hit the nail on the head; strName is not defined. But now I am way outside my depth, hope you can help

VBA Code: 
Sub TEST()

Application.ScreenUpdating = False
Sheets("Sheet1").Select
Sheets("Sheet1").Range("a:a").RowHeight = 18
Sheets("Sheet1").Range("a:u").Font.ColorIndex = vbBlack
Sheets("For Sheet1").Select
Application.GoTo ActiveSheet.Range("A1"), True
Sheets("Sheet1").Range("a:j,s:s,u:u").Copy Destination:=Sheets("For Sheet2").Range("A1")

'Delete Data Rows "For Sheet2" Sheet
Set h = Sheets("For Sheet2")
If h.AutoFilterMode Then h.AutoFilterMode = False
lr = Columns("A:L").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With h.Range("A6:L" & lr)

.AutoFilter Field:=2, Criteria1:="=*" & strName & ".xlsm", Operator:=xlAnd '<Delete Row If Contains: *.xlsm
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=2

.AutoFilter Field:=2, Criteria1:="=ABC" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: ABC
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=2

.AutoFilter Field:=12, Criteria1:="=XYZ" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: XYZ
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=12

.AutoFilter Field:=12, Criteria1:="=ABCXYZ" & strName & "*", Operator:=xlAnd '<Delete Row If Contains: ABCXYZ
.Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
.AutoFilter Field:=12

h.AutoFilterMode = False

Application.ScreenUpdating = True
End Sub
Click to expand...

Michael M said:
In the criteria you have "=*","=ABC","XYZ","=ABCXYZ"
What are you actually trying to delete in real terms !!
Is the "=" sign actually in the cells ??
Click to expand...

No. The text strings that indicate the rows to be deleted are "ABC", "XYZ", and "ABCXYZ" (without quotation marks)
 
Upvote 0
Can you post some real examples....desensitized of course....Have a look at using the XL2BB in my tag to do this.
Using this criteria.....Criteria1:="=*" could be throwing up enough confusion to delete everything !!
The "=" is not need within the quotes if it doesn't exist in the string search
 
Upvote 0
This is Sheet 1 the Base sheet for the data, from which Sheet 2 is extracted.

I tried deleting the "=" sign but that didn't fix the issue either, in fact it created an error.

Thanks 10^6

hip

Copy of Upcoming Schedule - July 22 2021.xlsm
ABCDEFGHIJKLMNOPQRSTU
1Upcoming Runs
2(To be used as a guideline. Subject to change.)
3Runs are subject to availability of space and may close earlier than dates shown below
4
5-14Days-7Days-14Days-2Days-8Days+4Days
6ABCDEFHIJKComments
7
8
9Grade 11Jul 30, 2021Aug 6, 2021Aug 13, 2021Aug 17, 2021Aug 19, 2021Aug 28, 2021Sep 3, 2021Sep 7, 2021
10Grade 12Sep 1, 2021Sep 8, 2021Sep 15, 2021Sep 17, 2021Sep 21, 2021Sep 29, 2021Oct 5, 2021Oct 9, 2021
11Grade 13Sep 30, 2021Oct 7, 2021Oct 14, 2021Oct 18, 2021Oct 20, 2021Oct 28, 2021Oct 31, 2021Nov 4, 2021
12
13Grade 21Aug 6, 2021Aug 13, 2021Aug 20, 2021Aug 24, 2021Aug 26, 2021Sep 3, 2021Sep 6, 2021Sep 10, 2021ABC
14Grade 22Sep 8, 2021Sep 15, 2021Sep 22, 2021Sep 24, 2021Sep 28, 2021Oct 6, 2021Oct 9, 2021Oct 13, 2021
15
16Grade 31Jul 5, 2021Jul 12, 2021Jul 19, 2021Jul 20, 2021Jul 22, 2021Aug 2, 2021Aug 6, 2021Aug 10, 2021XYZ
17Grade 33Aug 10, 2021Aug 17, 2021Aug 24, 2021Aug 27, 2021Aug 30, 2021Sep 7, 2021Sep 11, 2021Sep 15, 2021
18Grade 33Sep 10, 2021Sep 17, 2021Sep 24, 2021Sep 28, 2021Sep 30, 2021Oct 9, 2021Oct 13, 2021Oct 17, 2021
19
20Grade 41Jul 9, 2021Jul 16, 2021Jul 23, 2021Jul 27, 2021Jul 29, 2021Aug 6, 2021Aug 12, 2021Aug 16, 2021ABCXYZ
21Grade 42Aug 13, 2021Aug 20, 2021Aug 27, 2021Aug 31, 2021Sep 2, 2021Sep 11, 2021Sep 16, 2021Sep 20, 2021
22Grade 43Sep 16, 2021Sep 23, 2021Sep 30, 2021Oct 4, 2021Oct 6, 2021Oct 14, 2021Oct 18, 2021Oct 22, 2021
23
24Grade 51Sep 17, 2021Sep 24, 2021Oct 1, 2021Oct 5, 2021Oct 7, 2021Oct 17, 2021Oct 17, 2021Oct 21, 2021
25Text
26
27Grade 61Jul 23, 2021Jul 30, 2021Aug 6, 2021Aug 10, 2021Aug 12, 2021Aug 21, 2021Aug 25, 2021Aug 29, 2021
28Grade 62Aug 24, 2021Aug 31, 2021Sep 7, 2021Sep 10, 2021Sep 13, 2021Sep 21, 2021Sep 26, 2021Sep 30, 2021
29Grade 63Sep 24, 2021Oct 1, 2021Oct 8, 2021Oct 12, 2021Oct 14, 2021Oct 23, 2021Oct 28, 2021Nov 1, 2021
30
31Grade 71Jul 29, 2021Aug 5, 2021Aug 12, 2021Aug 16, 2021Aug 18, 2021Aug 26, 2021Aug 27, 2021Aug 31, 2021
32Grade 72Aug 27, 2021Sep 3, 2021Sep 10, 2021Sep 14, 2021Sep 16, 2021Sep 26, 2021Sep 29, 2021Oct 3, 2021
33
34Grade 82Jul 16, 2021Jul 23, 2021Jul 30, 2021Aug 3, 2021Aug 5, 2021Aug 13, 2021Aug 17, 2021Aug 21, 2021
35Grade 83Aug 19, 2021Aug 26, 2021Sep 2, 2021Sep 3, 2021Sep 8, 2021Sep 16, 2021Sep 20, 2021Sep 24, 2021
36Grade 84Sep 21, 2021Sep 28, 2021Oct 5, 2021Oct 5, 2021Oct 7, 2021Oct 19, 2021Oct 23, 2021Oct 27, 2021
37
38Grade 9Jul 22, 2021Jul 29, 2021Aug 5, 2021Aug 9, 2021Aug 11, 2021Aug 19, 2021Aug 20, 2021Aug 24, 2021
39Text
40
41*line 1
42**line 2
43***line 3
44
45AN/MNJuly 22, 2021
46G:\Recent Run July 22 2021.xlsm
47
48
49
50
51
Sheet 1
Cell Formulas
RangeFormula
E38,E34:E36,E31:E32,E27:E29,E24,E20:E22,E16:E18,E13:E14,E9:E11E9=WORKDAY(I9+$E$5+1,-1,Exclude_Holidays)
G38,G34:G36,G31:G32,G27:G29,G24,G20:G22,G16:G18,G13:G14,G9:G11G9=WORKDAY(I9+$G$5+1,-1,Exclude_Holidays)
I38,I34:I36,I31:I32,I27:I29,I24,I20:I22,I16:I18,I13:I14,I9:I11I9=WORKDAY(O9+$I$5+1,-1,Exclude_Holidays)
K38,K34:K36,K31:K32,K27:K29,K24,K20:K22,K16:K18,K13:K14,K9:K11K9=WORKDAY(M9+$K$5+1,-1,Exclude_Holidays)
M38,M34:M36,M31:M32,M27:M29,M24,M20:M22,M16:M18,M13:M14,M9:M11M9=WORKDAY(O9+$M$5+1,-1,Exclude_HolidaysAndFridays)
S38,S34:S36,S31:S32,S27:S29,S24,S20:S22,S16:S18,S13:S14,S9:S11S9=Q9+$S$5
Named Ranges
NameRefers ToCells
Exclude_Holidays='Excluded Dates'!$A$2:$A$53G13:G14, G16:G18, G24, K34:K36, I34:I36, G34:G36, E34:E36, K31:K32, I31:I32, G31:G32, E31:E32, K27:K29, I27:I29, G27:G29, E27:E29, K20:K22, I20:I22, G20:G22, E20:E22, K16:K18, I16:I18, E16:E18, K13:K14, I13:I14, E13:E14, K9:K11, I9:I11, G9:G11, E9:E11, E24, E38, G38, I24, I38, K24, K38, M34:M36, M31:M32, M27:M29, M20:M22, M16:M18, M13:M14, M9:M11, M24, M38
Exclude_Holidays_Fridays='Excluded Dates'!$A$2:$C$500G13:G14, G16:G18, G24, K34:K36, I34:I36, G34:G36, E34:E36, K31:K32, I31:I32, G31:G32, E31:E32, K27:K29, I27:I29, G27:G29, E27:E29, K20:K22, I20:I22, G20:G22, E20:E22, K16:K18, I16:I18, E16:E18, K13:K14, I13:I14, E13:E14, K9:K11, I9:I11, G9:G11, E9:E11, E24, E38, G38, I24, I38, K24, K38, M34:M36, M31:M32, M27:M29, M20:M22, M16:M18, M13:M14, M9:M11, M24, M38
Exclude_HolidaysAndFridays='Excluded Dates'!$A$2:$C$500G13:G14, G16:G18, G24, K34:K36, I34:I36, G34:G36, E34:E36, K31:K32, I31:I32, G31:G32, E31:E32, K27:K29, I27:I29, G27:G29, E27:E29, K20:K22, I20:I22, G20:G22, E20:E22, K16:K18, I16:I18, E16:E18, K13:K14, I13:I14, E13:E14, K9:K11, I9:I11, G9:G11, E9:E11, E24, E38, G38, I24, I38, K24, K38, M34:M36, M31:M32, M27:M29, M20:M22, M16:M18, M13:M14, M9:M11, M24, M38
Holidays='Excluded Dates'!$A$2:$A$53G13:G14, G16:G18, G24, K34:K36, I34:I36, G34:G36, E34:E36, K31:K32, I31:I32, G31:G32, E31:E32, K27:K29, I27:I29, G27:G29, E27:E29, K20:K22, I20:I22, G20:G22, E20:E22, K16:K18, I16:I18, E16:E18, K13:K14, I13:I14, E13:E14, K9:K11, I9:I11, G9:G11, E9:E11, E24, E38, G38, I24, I38, K24, K38, M34:M36, M31:M32, M27:M29, M20:M22, M16:M18, M13:M14, M9:M11, M24, M38
Holidays_Fridays='Excluded Dates'!$A$2:$C$500G13:G14, G16:G18, G24, K34:K36, I34:I36, G34:G36, E34:E36, K31:K32, I31:I32, G31:G32, E31:E32, K27:K29, I27:I29, G27:G29, E27:E29, K20:K22, I20:I22, G20:G22, E20:E22, K16:K18, I16:I18, E16:E18, K13:K14, I13:I14, E13:E14, K9:K11, I9:I11, G9:G11, E9:E11, E24, E38, G38, I24, I38, K24, K38, M34:M36, M31:M32, M27:M29, M20:M22, M16:M18, M13:M14, M9:M11, M24, M38
 
Upvote 0
you haven't really told me much ??
You want to delete the row if column "W" meets a filtered criteria ??
 
Upvote 0
Further to the above .....are you deleting a row if there is ANYTHING in col "W" of that row ??
 
Upvote 0
Michael M said:
you haven't really told me much ??
You want to delete the row if column "W" meets a filtered criteria ??
Click to expand...
That is it almost exactly, in some cases it is a text match in Field 2 (Col B), in other Cases it is a text match in Field 12 (Col L)

The problem as you know is that if there is not at least one case to be deleted the entire table from Row 6 onwards is deleted.

Thanks for sticking with this.

hip
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
167
DanteAmor
DanteAmor
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
446
Alex Blakenburg
A
hip2b2
  • Locked
  • Question
Autofiltering for Values Deletes All Rows
Replies
1
Views
596
Fluff
Fluff
M
  • Solved
Delete rows based on the value in a particular column
Replies
6
Views
435
manojrf
M
M
  • Question
Sorting data in a macro messes up formatting
Replies
2
Views
533
Alex Blakenburg
A

Forum statistics

Threads
1,214,592
Messages
6,120,433
Members
448,961
Latest member
nzskater

Share this page

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