Help Deleting Rows

[Dudely]

I've tried this two different ways, neither seems to work quite right.

Set sentWS = ThisWorkbook.Worksheets("Sent")
lastRow = sht.UsedRange.Rows.count 'last row of current sheet
currentRow = sentWS.UsedRange.Rows.count 'last row of sent sheet
currentRow = currentRow + 1
While lastRow > 1
Set cell = sht.Range("A" & lastRow)

cell.EntireRow.Copy sentWS.Range("A" & currentRow)

(1) cell.Rows(lastRow).Delete
(2) cell.EntireRow.Delete

currentRow = currentRow + 1
lastRow = lastRow - 1
Wend

The line labeled (1) above fails to do anything at all, and the line
labeled (2) deletes the first line instead of the last line.

The copy works just fine, as does the rest of the code.

So what am I doing wrong please?

Thank you

 

[Chip Pearson]

When deleting rows, it is always best to work from the bottom up,
rather than the top down.

Dim RowNdx As Long
For RowNdx = LastRow To 2 Step -1
If Something = True Then
Rows(RowNdx).Delete
End If
Next RowNdx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dudely]

When deleting rows, it is always best to work from the bottom up,
rather than the top down.

Thank you, your help is most welcome.

If you look at my code carefully, you'll notice that I do indeed start
from the bottom when deleting. The copying goes in the forward
direction so that the new page gets filled from the top down, while
deletions on the old page start from the bottom.


I'm assuming your code works. However, I don't see much of a
difference between what you do and what I do. The primary difference
seems to be that I fully qualify the row (cell.Rows(lastRow).Delete),
while you don't (Rows(RowNdx).Delete

So could you please be so kind as to explain why my version doesn't
work so that I can understand what's going on?

For the record, I have "sht" set to ThisWorkbook.worksheets("raw")
elsewhere in the code.

Thank you

 

[Don Guillett]

Sub yoursmodified()
Set sentws = ThisWorkbook.Worksheets("Sent")
lastRow = ActiveSheet.UsedRange.Rows.Count 'last row of current sheet
currentRow = sentws.UsedRange.Rows.Count 'last row of sent sheet
currentRow = currentRow + 1
While lastRow > 1
Set cell = Range("A" & lastRow)

cell.EntireRow.Copy sentws.Range("A" & currentRow)

' cell.Rows(lastRow).Delete
cell.EntireRow.Delete

currentRow = currentRow + 1
lastRow = lastRow - 1
Wend
End Sub

'rows 1,2,3 becomes 3,2,1
Sub better() Moves last row from source to 2nd row of new sheet,etc
Set sentws = ThisWorkbook.Worksheets("Sent")
slr = ActiveSheet.UsedRange.Rows.Count
'or slr=cells(rows.count,"a").end(xlup).row
For i = slr To 2 Step -1
dlr = sentws.UsedRange.Rows.Count + 1
'or dlr=sentws.cells(rows.count,"a").end(xlup).row+1
Rows(i).Cut Destination:=sentws.Rows(dlr)
Next i
End Sub

 

[Dudely]

Thanks but I fail to see any significant difference between your
modified code and my original code. In fact, the ONLY difference that
I see is you're using "Activesheet" to initialize lastRow, whereas I'm
specifically using the named sheet (sht). I wish this newsreader
supported colors so things were easier to see. I also notice that you
- like Chip - also removed the fully qualified reference to cell, and
instead used the "active sheet" function "Range" instead of
"sht.Range" like I do.

So, assuming your code works, then my question remains. Why???

I in fact used Chip's modification to replace my code, in particular I
replaced the line cell.Rows(lastRow).Delete with Rows(lastRow).Delete
and it works. I made NO other changes. So what is the difference
between the two lines of code and why does one work but not the
other???

Thank you

 

[Dudely]

Thanks but I fail to see any significant difference between your
modified code and my original code.  In fact, the ONLY difference that
I see is you're using "Activesheet" to initialize lastRow, whereas I'm
specifically using the named sheet (sht).  I wish this newsreader
supported colors so things were easier to see.  I also notice that you
- like Chip - also removed the fully qualified reference to cell, and
instead used the "active sheet" function "Range" instead of
"sht.Range" like I do.

So, assuming your code works, then my question remains.  Why???

I in fact used Chip's modification to replace my code, in particular I
replaced the line cell.Rows(lastRow).Delete with Rows(lastRow).Delete
and it works.  I made NO other changes.  So what is the difference
between the two lines of code and why does one work but not the
other???

Thank you









- Show quoted text -

All this time and still not a single response? You guys don't know?
Seriously?

I bring this up again, because the problem has returned, except it's
mutated a bit. I moved my code above into it's own separate function,
and now even Chip's method (in the manner I used/copied it) fails to
delete a row. However, my original method now works. What in the
world is going on here? The ONLY changes I made to the code is I now
pass "sht" in as a parameter, and the other variables are local in
scope (which they were before also, just in a different function).
<Insert Twilight Zone theme here>