Copy rows to bottom of sheet based on cell value and sorted ascending wise

I have an Excel sheet which is pulled from JIRA. This sheet has variable rows each week. Once it is pulled, I have a macro which performs various actions. One of these is to move certain rows to bottom of the sheet based on a value present in Column 'F'. In this particular case, if the value 'RCR' is present in column 'F', then that particular row should cut and paste at the bottom.

For this I have written the below code. This code works well and does the job. But the issue is since it loops from bottom to top, the list of rows with 'RCR' values is in a descending manner. But I want the rows to be sorted in an ascending manner.

If I use "1 to lastRowOne" in the For loop, then what happens is the row gets deleted after the move has been done, due to this, if the next row also has the value of 'RCR', that particular row is skipped because it takes the place of the deleted row. So the macro moves to the row after that, which is causing the macro to miss certains rows having consecutive value = 'RCR'.

Dim wsOne As Worksheet
Dim lastRowOne As Long
Dim lastRowTwo As Long

Set wsOne = ActiveWorkbook.Sheets("Status")

lastRowOne = wsOne.Cells(wsOne.Rows.Count, 1).End(xlUp).Row
lastRowTwo = wsOne.Cells(wsOne.Rows.Count, 1).End(xlUp).Row + 1

For I = lastRowOne To 1 Step -1
    If wsOne.Range("F" & I).Value = "RCR" Then
        wsOne.Rows(lastRowTwo).Value = wsOne.Rows(I).Value
        wsOne.Rows(I).EntireRow.Delete
    End If
Next

Is there a way that this can be remedied?



Read more here: https://stackoverflow.com/questions/65722598/copy-rows-to-bottom-of-sheet-based-on-cell-value-and-sorted-ascending-wise

Content Attribution

This content was originally published by RB49 at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: