|
本帖最后由 leoxxx 于 2023-7-10 00:19 编辑
- Sub t1()
- Columns(1).ClearContents '清除区域中的公式和值
- Intersect(Columns(1), Columns("B:D").SpecialCells(2).EntireRow) = 1 'A列与B:D列中非空单元格所在的行的交集
- End Sub
- Sub t2()
- Dim i As Integer, wb As Workbook, rg As Range
- ThisWorkbook.Sheets(2).UsedRange.ClearContents '清除区域中的公式和值
- Set wb = Workbooks.Open(ThisWorkbook.Path & "\\A.xls")
- For i = 1 To wb.Sheets.Count
- If i = 1 Then
- wb.Sheets(i).UsedRange.Copy
- ThisWorkbook.Sheets(2).[a1].PasteSpecial Paste:=xlPasteValues '复制值到目标表格
- Else
- Set rg = wb.Sheets(i).UsedRange.End(xlDown).End(xlToRight) '选中区域的最后一个单元格
- wb.Sheets(i).UsedRange.Range("a2", rg).Copy '复制除第一行以外的区域
- ThisWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
- '粘贴值到目标表格非空单元格的下面一格
- End If
- Next i
- wb.Close False
- End Sub
复制代码 |
|