Help w/ Loop

[YellowLabPro]

My error is at
r.EntireRow.delete

[VBA]
Sub delete()
Dim r As Long
Dim lrow As Long
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks("Tgsupdater.xls")
Set ws1 = Worksheets("Update")
lrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lrow
If Cells(r, "G").Value > 998 Then r.EntireRow.delete
Next r
End Sub

[/VBA]

thx,

YLP

[mdmackillop]

Hi Yelp,
[VBA]r.EntireRow.delete
[/VBA]
r (or somthing else) needs to be a Range, not a Long
You also need to delete from the bottom up.

[YellowLabPro]

Hi Md-
[VBA]
Sub delete()
Dim r As Range
Dim lrow As Long
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks("Tgsupdater.xls")
Set ws1 = Worksheets("Update")
lrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For r = 1 To lrow
If Cells(r, "G").Value > 998 Then r.EntireRow.delete
Next r
End Sub
[/VBA]

I had tried Dim r as range but that failed too. I hate to cheat today and ask, but could you walk me through this .... I have some pressing things I need to handle today.... and this little bugger is now messing w/ me

[mdmackillop]

You are carefully specifying your workbook/sheet, but not linking them, and also not linking these to the Cells range. Your code will delete rows from the activesheet in the activeworkbook, which may not be the one intended.
I'm assuming you are intending to check the last value in column A, and not column G where your numbers are, otherwise lrow might be 1.

[VBA]
Sub delete()
Dim r As Long
Dim lrow As Long
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks("Tgsupdater.xls")
Set ws1 = wb1.Worksheets("Update")
With ws1
lrow = .Cells(Rows.Count, "A").End(xlUp).Row '???
For r = lrow To 1 Step -1
If .Cells(r, "G").Value > 998 Then
.Cells(r, "G").EntireRow.delete
End If
Next r
End With
End Sub

[/VBA]

[YellowLabPro]

Type Mismatch
[VBA]For r[/VBA]


[VBA]
Sub delete()
Dim r As Range
Dim lrow As Long
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks("Tgsupdater.xls")
Set ws1 = Worksheets("Update")
lrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For r = lrow To 1 Step -1
If Cells(r, 8).Value > 998 Then r.Rows.EntireRow.delete
Next r
End Sub

[/VBA]

[YellowLabPro]

Md,
Sorry, my screen had not refreshed. Let me go back and read your last post.

[Bob Phillips]

[vba]

Sub delete()
Dim r As Range
Dim lrow As Long
Dim wb1 As Workbook
Dim ws1 As Worksheet
Set wb1 = Workbooks("Tgsupdater.xls")
Set ws1 = Worksheets("Update")
lrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For r = lrow To 1 Step -1
If Cells(r, 8).Value > 998 Then Rows(r).delete
Next r
End Sub
[/vba]

[mdmackillop]

[vba]

If Cells(r, 8).Value > 998 Then Rows(r).EntireRow.delete
[/vba]

[YellowLabPro]

Thanks Md-
Sorry I had to ask for the help there...
It is still not entirely clear.

I would like to keep this open to discuss w/ you a few of the points I missed.

Also-
Here is some code that you helped me develop a few days ago.
There is something that is baffeling me-

I have commented the line
[VBA]'WbTGSU.Close SaveChanges:=True[/VBA]
But it continues to close, rather than stay open.

This section of the code instead is telling the last sheet to stay open, ImportPriceLables.dat. Since I commented the WbTGSU I thought it would stay open and all others would close.
Can you shed some light here?

[VBA]
.SaveAs Filename:="C:\TGSFiles\ImportPriceLabels.dat", _
FileFormat:=xlCSV

End With
ActiveWindow.Close
'WbTGSU.Close SaveChanges:=True
Application.DisplayAlerts = True
End Sub
[/VBA]

[VBA]
Option Explicit
Sub TGSUpload()
Dim WbTGSU As Workbook
Dim WsU As Worksheet
Dim lrow As Long, r As Long
On Error Resume Next
Workbooks("TGSUpdater.xls").Activate
If Err <> 0 Then Workbooks.Open ("C:\TGSFiles\TGSUpdater.xls")
On Error GoTo 0
Set WbTGSU = Workbooks("TGSUpdater.xls")
Set WsU = WbTGSU.Worksheets("Update")
lrow = WsU.Cells(Rows.Count, "A").End(xlUp).Row
Application.DisplayAlerts = False
WbTGSU.Save
WsU.Activate
WsU.Copy
With WsU
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "h-mm-ss am/pm m-dd-yy") & ".csv", _
FileFormat:=xlCSV
WsU.Rows(1).delete
.SaveAs Filename:="C:\TGSFiles\Importin- " & Format(Now, "h-mm-ss am/pm m-dd-yy") & ".dat", _
FileFormat:=xlCSV
.SaveAs Filename:="C:\TGSFiles\Import.dat", _
FileFormat:=xlCSV
With WsU
lrow = .Cells(Rows.Count, "A").End(xlUp).Row '???
For r = lrow To 1 Step -1
If .Cells(r, "G").Value > 998 Then
.Cells(r, "G").EntireRow.delete
End If
Next r
End With
.SaveAs Filename:="C:\TGSFiles\ImportPriceLabels.dat", _
FileFormat:=xlCSV

End With
ActiveWindow.Close
'WbTGSU.Close SaveChanges:=True
Application.DisplayAlerts = True
End Sub
[/VBA]

[YellowLabPro]

To Xld:
Bob, I am sorry, my screen looked like MD had given me the solution and I went back to read it again and realized you had provided the code. Thanks for helping out w/ this.

I have some follow up questions regarding the way some of the things were occurring. But I want to study the code some first.

Regards,

YLP