仅对包含数据的行复制和粘贴Excel宏

1 回答

• -1

  你的问题有点令人困惑，但这可能是你想要开始的地方:(如果你能提供一个带有你想要的输出的示例表，那会有帮助）

  Sub copyRange()
      Dim dataSht As Worksheet, comboSht As Worksheet
      Set dataSht = Sheets("DataEntry")
      Set comboSht = Sheets("DataCombined")
  
      Dim rngToCopy As Range
  
      Set rngToCopy = dataSht.Range(Cells(2, 1), Cells(getLastFilledRow(dataSht), "J"))
      rngToCopy.Copy
      comboSht.Range("A" & (getLastFilledRow(comboSht) + 1)).PasteSpecial xlPasteValues
      rngToCopy = dataSht.Range(Cells(2, 1), Cells(getLastFilledRow(dataSht), "J")).SpecialCells(xlCellTypeConstants)
      rngToCopy.ClearContents
  End Sub
  
  'Gets the last row of the worksheet that contains data
  Public Function getLastFilledRow(sh As Worksheet) As Integer
      On Error Resume Next
      getLastFilledRow = sh.Cells.Find(What:="*", _
                              After:=sh.Range("A1"), _
                              LookAt:=xlPart, _
                              LookIn:=xlValues, _
                              SearchOrder:=xlByRows, _
                              SearchDirection:=xlPrevious, _
                              MatchCase:=False).Row
      On Error GoTo 0
  End Function
  

  回复于 2024-04-28T14:49:09+08:00