如何让Excel VBA代码基于值选择单行并迁移数据？

Hey there! Let's fix up your VBA code to handle single-row selection properly, make it cleaner, and set you up for adding the "On-going" functionality later. Your current code only targets row 2, so we'll adjust it to loop through all relevant rows while cutting out unnecessary steps.

Key Optimizations We'll Make #

• Ditch the unnecessary Select statements (they slow down code and are rarely needed)
• Copy entire row ranges at once instead of individual cells for efficiency
• Loop through all rows with data (not just row 2)
• Batch clear operations to simplify the code
• Build in a structure that makes adding "On-going" handling trivial later

Optimized Code #

Sub MoveBasedOnValueOptimized()
    Dim wsSource As Worksheet, wsArchive As Worksheet, wsOngoing As Worksheet
    Dim lastRow As Long, i As Long
    Dim destRowArchive As Long
    
    ' Set worksheet references (use your actual sheet names here!)
    Set wsSource = ThisWorkbook.Worksheets("Cjob")
    Set wsArchive = ThisWorkbook.Worksheets("CArc")
    Set wsOngoing = ThisWorkbook.Worksheets("Ccon") ' For future "On-going" handling
    
    ' Find the last row with data in the source sheet (checking column G)
    lastRow = wsSource.Cells(wsSource.Rows.Count, "G").End(xlUp).Row
    
    ' Loop backwards from last row to row 2 (avoids skipping rows if you delete later)
    For i = lastRow To 2 Step -1
        Select Case wsSource.Cells(i, "G").Value
            Case "Done"
                ' Get the next empty row in the archive sheet
                destRowArchive = wsArchive.Cells(wsArchive.Rows.Count, "A").End(xlUp).Row + 1
                
                ' Copy columns A-F of the current row to the archive sheet
                wsSource.Range("A" & i & ":F" & i).Copy Destination:=wsArchive.Range("A" & destRowArchive)
                
                ' Clear the source row's data (columns A-F)
                wsSource.Range("A" & i & ":F" & i).ClearContents
                
                ' Optional: Delete the empty row from source (uncomment if needed)
                ' wsSource.Rows(i).Delete
            
            Case "On-going"
                ' Add your "On-going" logic here later (example structure below)
                ' Dim destRowOngoing As Long
                ' destRowOngoing = wsOngoing.Cells(wsOngoing.Rows.Count, "A").End(xlUp).Row + 1
                ' wsSource.Range("A" & i & ":F" & i).Copy Destination:=wsOngoing.Range("A" & destRowOngoing)
                ' wsSource.Range("A" & i & ":F" & i).ClearContents
        End Select
    Next i
    
    MsgBox "Row migration finished successfully!", vbInformation
End Sub

Breakdown of Key Changes #

1. Worksheet References: Using explicit sheet names (like "Cjob") instead of Sheet4 makes the code more reliable—sheet index numbers can change if you reorder sheets.
2. Backwards Loop: By looping from the last row up to row 2, we avoid skipping rows if you decide to delete empty rows after clearing data (since deleting a row shifts all lower rows up).
3. Batch Operations: Copying and clearing entire ranges (A:F) is way faster than handling each cell individually, and makes the code shorter.
4. Select Case Structure: This makes it super easy to add the "On-going" handling later—just uncomment the example code in that case block and tweak it to your needs.
5. Dynamic Destination Rows: Using End(xlUp).Row +1 ensures we always target the next empty row in the target sheet, even if the first few rows are empty.

Quick Notes #

• Double-check that the sheet names in the code match your actual worksheet names.
• If you don't want to leave empty rows in the source sheet after moving data, uncomment the wsSource.Rows(i).Delete line.
• Always test this code on a copy of your data first to avoid accidental data loss!

内容的提问来源于stack exchange，提问作者Samuel Kinrade