Code works with F8 but not when I just run it

[zookeepertx]

I'm sure this ihas been answered before, but I can't find it; sorry.

I've got this macro that USED to work fine, but was unfinished. I haven't gotten back to finish it till just today and, when I started playing with it, it wouldn't work right. So, I've spent the day finding and fixing various little problems but still have one left. If I F8 through the code, everything works just fine but, if I just run it like normal, part of it just does not carry out its purpose in life, LOL! Here's the start of my code, including the trouble spot:

Sub Roster()
'Roster Macro-Jenny 7028014
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayAlerts = False
End With
Worksheets.Add(After:=Worksheets(1)).Name = "BG"
Worksheets.Add(After:=Worksheets(1)).Name = "West"
Worksheets.Add(After:=Worksheets(1)).Name = "East"
lr = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row
lr3 = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row
'text-to-columns on raw data
Sheets(1).Range("A1:A" & lr).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2), Array(6, 2), _
        Array(7, 2)), TrailingMinusNumbers:=True
'Delete extra header rows throughout report
[B][COLOR=#FF0000]lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 8 Step -1
    If Range("A" & i).Value Like "P.*" Then
        Rows(i - 1 & ":" & i + 20).Delete
    End If
Next i[/COLOR][/B]

The original report is usually 7-9 pages long and, after the text-to-columns, each page's header section takes up 21 rows on the spreadsheet. I only want to keep the FIRST set of headers (in yellow below) because it's all going to be one spreadsheet and won't need any more headers ( in grey below).

I've got a very shortened form of the sheet to play with. If I just run the code, the deletion of those 21 rows simply doesn't happen, but if I F8 through it works fine. The really weird thing is, if I F8 through to the first set of "extra" headers and let it delete them, then let the macro continue on its own, it continues fine.

I'll post as short a version of my sheet here as I can:

6/30/2014	Page	1	of	9
P.	O.	Box	5
Los	Angeles,	CA	90051-5176
Phone	()	Fax	()
B90	-	Monthly	Roster,	East	Coast
Performance	Team
Roster	of	Trailer	Manifests
Manifest	#
Load	#
Created
Operator
Consolidator
Consignee
Trailer
LH	Carrier
Loaded
Closed
Departed
Arrived
Ctns	Pcs	Lbs	PU$	Cons$	LH$	FSC	Total
Inv	#
Spot
1272129	John	Smith
8199
5866	5/27/2014	4:56	PM
5/28/2014 0:00	9:17:00	PM
5/28/2014 0:00	11:56:00	PM
FLY
5/27/2014	582	0	7999	267.81	2,399.81	439.87
cessua	John	Smith-	Direct
E0180202	136.67	35.39
6/30/2014	Page	2	of	9
P.	O.	Box	5
Los	Angeles,	CA	90051-5176
Phone	()	Fax	()
B90	-	Monthly	Roster,	East	Coast
Performance	Team
Roster	of	Trailer	Manifests
Manifest	#
Load	#
Created
Operator
Consolidator
Consignee
Trailer
LH	Carrier
Loaded
Closed
Departed
Arrived
Ctns	Pcs	Lbs	PU$	Cons$	LH$	FSC	Total
Inv	#
Spot
1277061	John	Smith
8265
40818	6/10/2014	10:40	PM
6/10/2014 0:00	11:28:00	PM
6/10/2014 0:00	11:52:00	PM
CVEN
6/10/2014	204	0	2338	78.8	701.4	150.28
cessua	John	Smith-	Pinnacle	Point
E0182176	57	14.48
6/30/2014	Page	6	of	9
P.	O.	Box	5
Los	Angeles,	CA	90051-5176
Phone	()	Fax	()
B90	-	Monthly	Roster,	East	Coast
Performance	Team
Roster	of	Trailer	Manifests
Manifest	#
Load	#
Created
Operator
Consolidator
Consignee
Trailer
LH	Carrier
Loaded
Closed
Departed
Arrived
Ctns	Pcs	Lbs	PU$	Cons$	LH$	FSC	Total
Inv	#
Spot
1282592	John	Smith
8341
5868	6/25/2014	11:00	AM
6/25/2014 0:00	8:14:00	PM
6/25/2014 0:00	11:36:00	PM
SUP
6/27/2014	12	0	184	5.94	55.2	5.94
ewewro	John	Smith-	ECDC
E0184691	0	0

<colgroup><col style="width: 73pt; mso-width-source: userset; mso-width-alt: 4138;" width="97"> <col style="width: 57pt; mso-width-source: userset; mso-width-alt: 3242;" width="76"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2858;" width="67"> <col style="width: 64pt; mso-width-source: userset; mso-width-alt: 3626;" width="85"> <col style="width: 43pt; mso-width-source: userset; mso-width-alt: 2432;" width="57"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2816;" width="66"> <col style="width: 43pt; mso-width-source: userset; mso-width-alt: 2432;" width="57"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 2176;" width="51"> <tbody>
</tbody>

Strange, huh? I don't see how it can F8 perfectly, but not work 10 seconds later just running it!

Thanks for your help!

Jenny

 

[RickXL]

Hi,

I expect it because you have not specified the sheet to use on the Range and Rows definitions.

When you step through you are on the right sheet.

 

[zookeepertx]

Hi,

I expect it because you have not specified the sheet to use on the Range and Rows definitions.

When you step through you are on the right sheet.

Hi Rick

No, because I always start out from sheet 1 no matter whether I'm trying to run it or F8 through. Actually, I usually take my original, untouched sheet and create a new workbook with only that sheet in it and start from there, letting the macro create the new sheets.
I'm afraid it's gotta be something else.

Thanks

Jenny

 

[RickXL]

I copied your data and code, made the changes I suggested, and it worked for me.

Before the changes, the final lr is set to the last row on the sheet named "East". So the macro only checks rows 1 to 8 for the P.

 

[zookeepertx]

I copied your data and code, made the changes I suggested, and it worked for me.

Before the changes, the final lr is set to the last row on the sheet named "East". So the macro only checks rows 1 to 8 for the P.

Oh, duhhhh, I see it now! Sometimes I read too fast and don't take in what I just saw! I won't get to try it until I get back into work on Monday, but it sure looks like it'll work right with your changes. I should know better than making that mistake.
I'll be sure to let you know how it turns out after that.

Thanks for your help and your patience with me!

Jenny

 

[RickXL]

No problem.

You know where I am if it doesn't work

Regards,