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

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)


On Tue, 21 Oct 2008 11:37:35 -0700 (PDT), Dudely
wrote:

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

On Oct 21, 1:22*pm, Chip Pearson wrote:
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


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, LLCwww.cpearson.com
(email on web site)

On Tue, 21 Oct 2008 11:37:35 -0700 (PDT), Dudely
wrote:



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- Hide quoted text -

- Show quoted text -

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


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dudely" wrote in message
...
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

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


On Oct 21, 3:55*pm, "Don Guillett" wrote:
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Dudely" wrote in message

...



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- Hide quoted text -

- Show quoted text -

On Oct 21, 5:40*pm, Dudely wrote:
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

On Oct 21, 3:55*pm, "Don Guillett" wrote:



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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
"Dudely" wrote in message

....

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- Hide quoted text -

- Show quoted text -- Hide quoted text -

- 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