首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >搜索范围内不为空的单元格,粘贴到新工作表

问搜索范围内不为空的单元格,粘贴到新工作表
EN

Stack Overflow用户
提问于 2021-11-13 01:30:43
回答 2查看 425关注 0票数 0

在Excel中,我需要一个VBA宏来执行以下操作:

  1. 搜索"Sheet2“范围A2:Q3500以查找任何包含数据的单元格(不为空),并复制只对那些单元格进行。

  1. 将这些单元格的确切值粘贴到以单元格A2.

开头的"Sheet3“中。

当我说“确切值”时,我的意思是单元格中的文本/数字与复制时显示的文本/数字完全相同,没有不同的格式应用。

如有任何指导,将不胜感激,谢谢!

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2021-11-14 09:28:19

复制过滤数据

  • 下面将复制完整的表范围,然后删除“空”行。
  • 调整常量部分中的值。

代码语言:javascript
复制
Option Explicit

Sub CopyFilterData()

    ' Source
    Const sName As String = "Sheet2"
    Const sFirst As String = "A1"
    ' Destination
    Const dName As String = "Sheet3"
    Const dFirst As String = "A1"
    Const dfField As Long = 1
    Const dfCriteria As String = "="
    ' Both
    Const Cols As String = "A:Q"
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    If sws.AutoFilterMode Then sws.AutoFilterMode = False
    Dim sfCell As Range: Set sfCell = sws.Range(sFirst)
    
    Dim slCell As Range
    With sfCell.Resize(sws.Rows.Count - sfCell.Row + 1)
        Set slCell = .Find("*", , xlFormulas, , , xlPrevious)
    End With
    If slCell Is Nothing Then Exit Sub ' no data in column range
    
    Dim rCount As Long: rCount = slCell.Row - sfCell.Row + 1
    If rCount = 1 Then Exit Sub ' only headers
    
    Dim scrg As Range: Set scrg = sfCell.Resize(rCount) ' Criteria Column Range
    Dim srg As Range: Set srg = scrg.EntireRow.Columns(Cols) ' Table Range
    Dim cCount As Long: cCount = srg.Columns.Count
    
    Application.ScreenUpdating = False
    
    ' Destination
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    If dws.AutoFilterMode Then dws.AutoFilterMode = False
    dws.UsedRange.Clear
    Dim dfcell As Range: Set dfcell = dws.Range(dFirst)
    Dim drg As Range: Set drg = dfcell.Resize(rCount, cCount) ' Table Range
    
    srg.Copy drg ' copy
    
    Dim ddrg As Range: Set ddrg = drg.Resize(rCount - 1).Offset(1) ' Data Range
    
    drg.AutoFilter dfField, dfCriteria
    
    Dim ddfrg As Range ' Data Filtered Range
    On Error Resume Next
        Set ddfrg = ddrg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    dws.AutoFilterMode = False
    
    If Not ddfrg Is Nothing Then
        ddfrg.EntireRow.Delete ' delete 'empty' rows
    End If
    
    'drg.EntireColumn.AutoFit
    'wb.Save
    
    Application.ScreenUpdating = True
    
    MsgBox "Data copied.", vbInformation, "Copy Filtered Data"

End Sub
票数 0
EN

Stack Overflow用户

发布于 2021-11-13 03:39:09

下面的代码应该对你有帮助。

代码语言:javascript
复制
Sub CopyNonEmptyData()
    
    Dim intSheet3Row As Integer
    intSheet3Row = 2
    
    For Each c In Range("A2:Q3500")
        If c.Value <> "" Then
            Sheets("Sheet3").Range("A" & intSheet3Row).Value = c.Value
            intSheet3Row = intSheet3Row + 1
        End If
    Next c
    
End Sub
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/69950744

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档