excel - 从 A :F where it indicates "This is not a date format" and paste the values in a specific column 复制行值

Question

在我的代码中，它标识了 A 列“这是日期格式”和“这不是日期格式”中的每个值。但我需要它从 A:F 复制值在它声明“这不是日期格式”的行中，并将其粘贴到它上面的特定单元格中。偏移量(-1, 2)。还要在复制的值之后删除单元格“这不是日期格式”的行。任何想法谢谢。下面是我的代码:

  Dim strDate As String


 Dim rng As Range, cell As Range
 
 Set rng = Range("A2:A18")

With ThisWorkbook.Worksheets("Feuil1")

    For Each cell In rng
    MsgBox (cell.Value)
      
    strDate = cell.Value
    
    If IsDate(strDate) Then
        MsgBox "This is a date format"
        
        
        Else
        MsgBox "This is not a date format"
        
           
        'copy cell from A:E
        
    Range("A" & ActiveCell.Row & ":F" & ActiveCell.Row).Copy
    
    
    
    'Paste selected and copied in specific cell in offset(Row, Column)
    
    
    Range("K" & ActiveCell.Row).Offset(-1, 2).PasteSpecial
  
        
        'copy cell from A:E
      
        
    
    
         'Paste selected and copied in specific cell in offset(Row, Column)
         
         
         
         
    End If
    Next cell
    
  End With
End Sub

Answer 1

请尝试下一个代码。它适用于所有 A:A 范围(带有数据)。如果您只需要 A18，请更改 lastRow = 18 :

Sub testCopyNotDate_DeleteRow()
  Dim sh As Worksheet, strDate As String, rngDel As Range
  Dim lastRow As Long, i As Long

   Set sh = ThisWorkbook.Worksheets("Feuil1")
   lastRow = sh.Range("A" & Rows.count).End(xlUp).Row
 
   For i = 2 To lastRow
        If Not IsDate(sh.Range("A" & i).Value) Then
            sh.Range("A" & i & ":F" & i).Copy Destination:=sh.Range("A" & i).Offset(-1, 2)
            If rngDel Is Nothing Then
                Set rngDel = sh.Range("A" & i)
            Else
                Set rngDel = Union(rngDel, sh.Range("A" & i))
            End If
        End If
   Next i
   If Not rngDel Is Nothing Then rngDel.EntireRow.Delete xlUp
End Sub

一次删除行会非常快(对于大范围)......