when deleting rows using a for/next loop, you need to start at the highest
row number

eg
for thisrow = 100 to 5 step -1
IF {condition} then
rows(thisrow).Delete
End If
next

if you loop "normally" then when you delete a row, higher numbered rows
move, and you'll miss rows. for example if you are on row 6 and you delete
it, row 7 becomes row 6 , row 8 becomes 7 and so on. Now when the NEXT row
is examonibed, row 7, you're actually on what was row 8, because row 7 moved
to replace the deleted row 6. phew. is this clear??


"Bishop" wrote in message
...
Changing to Worksheets did the trick. But now I can't delete the selected
rows. Here's my code:

Sub DirectorFormat()

Dim TSLastPFRow As Integer 'Tally Sheet
Dim TSPFTotal As Integer 'Tally Sheet PF
Dim ZeroRow As Long, i As Long
Dim Rng As Excel.Range

With Sheets("Tally Sheet")
.Cells.Copy
End With

With Worksheets("DirectorCopy")
'.Shapes("LazyEyeButton").Cut
.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
.Cells.Copy
.Cells.PasteSpecial Paste:=xlPasteValues,
Operation:=xlPasteSpecialOperationNone, _
SkipBlanks:=False, Transpose:=False
For i = 4 To Rows.Count Step 8
If Cells(i, "A").Value = 0 Then
ZeroRow = i
Exit For
End If
Next
TSLastPFRow = ZeroRow - 9
TSPFTotal = (Val(Replace(Cells(TSLastPFRow, 1).Value, "_PF", "")))
.Rows(ZeroRow & ":515").Delete
For i = 13 To (ZeroRow - 7) Step 8
If TypeName(Rng) = "Nothing" Then
Set Rng = .Cells(i, 1).EntireRow
Debug.Print Rng.Address
Else
Set Rng = Application.Union(Rng, .Cells(i, 1).EntireRow)
End If
Next
.Rng.Delete
.Rows("6:6").Freeze
End With
End Sub

When the last For loop finishes it still appears as though the entire
worksheet is selected... not just the rows. But I can't confirm because
the
.Delete never executes.

I've tried:

.Rng.Delete
.Rows(Rng).Delete
.Selection.Delete
.Delete
(This deletes the whole worksheet)

same problem here. Object does not support property or method. What do I
do?

"Rick Rothstein" wrote:

The Paste Method applies to Worksheets, Charts and some other objects,
but
not the Sheets object. I think that is because a Sheets object can
contain
either a Worksheet or a Chart, but since the Paste Method applies to
Worksheets and Charts individually, I'm guessing the Sheets object is too
general for the underlying code to work with. Try using...

With Worksheets("DirectoryCopy")

and see if that makes your code work.

--
Rick (MVP - Excel)


"Bishop" wrote in message
...
I have the following code:

With Sheets("DirectorCopy")
.Paste

Why doesn't this work? Keep getting Object doesn't support property or
method. I've tried:

.Cells.Paste
.Paste
.ActiveWorksheet.Paste
.Paste("A1")
.ActiveSheet.Paste Destination:=Worksheets("DirectorCopy").Range("A1" )
(which is how the help file says to do it)

None of these work.

what will work here?