PDA

View Full Version : Help w/ Loop



YellowLabPro
03-28-2007, 09:16 AM
My error is at
r.EntireRow.delete


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



thx,

YLP

mdmackillop
03-28-2007, 09:25 AM
Hi Yelp,
r.EntireRow.delete

r (or somthing else) needs to be a Range, not a Long
You also need to delete from the bottom up.

YellowLabPro
03-28-2007, 09:35 AM
Hi Md-

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


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
03-28-2007, 09:39 AM
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.


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

YellowLabPro
03-28-2007, 11:03 AM
Type Mismatch
For r



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

YellowLabPro
03-28-2007, 11:04 AM
Md,
Sorry, my screen had not refreshed. Let me go back and read your last post.

Bob Phillips
03-28-2007, 11:21 AM
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

mdmackillop
03-28-2007, 11:42 AM
If Cells(r, 8).Value > 998 Then Rows(r).EntireRow.delete
:rotlaugh:

YellowLabPro
03-28-2007, 12:00 PM
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
'WbTGSU.Close SaveChanges:=True
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?


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

End With
ActiveWindow.Close
'WbTGSU.Close SaveChanges:=True
Application.DisplayAlerts = True
End Sub



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

YellowLabPro
03-28-2007, 12:27 PM
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