复制列中的所有单元格 [英] Copy all the cells in a column

问题描述

我不能使用 End（xlDown）或 End（xlUp）获取整个范围，因为有空行。

如何复制最后一个有列数据的范围？

另外，

请注意，如何在Excel VBA中执行此操作。

解决方案

复制它们的最佳方法是使用自动过滤器并在空白处进行过滤，然后复制可见范围

例如

  Sub Sample（）
 Dim ws As Worksheet 
 Dim lRow As Long 
 Dim rngToCopy As Range，rRange作为范围
 
设置ws =表（Sheet1）
 
与ws 
 lRow = .Range（A& .Rows.Count）.End （xlUp）.Row 
 
设置rRange = .Range（A1：A& lRow）
 
'~~>删除任何过滤器
 .AutoFilterMode = False 
 
使用rRange'Filter，offset（以排除头文件）并复制可见行
 .AutoFilter字段：= 1，Criteria1：=< ;>中
设置rngToCopy = .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow 
结束
 
'~~>删除任何过滤器
 .AutoFilterMode = False 
 
 rngToCopy.Copy 
 
'
'~~>其他代码
'
结束
 End Sub 
  

I have a sheet which has 200 rows with some blanks in between.

I cannot use End(xlDown) or End(xlUp) to get the entire range because there are blank rows.

How can I copy up to the last range which has data in columns?

Also, I don't want to copy the headers.

Kindly suggest how I can do this in Excel VBA.

解决方案

The best way to copy them is to use Autofilter and filter them on Blanks and then copy the visible range

For Example

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rngToCopy As Range, rRange As Range

    Set ws = Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Set rRange = .Range("A1:A" & lRow)

        '~~> Remove any filters
        .AutoFilterMode = False

        With rRange 'Filter, offset(to exclude headers) and copy visible rows
            .AutoFilter Field:=1, Criteria1:="<>"
            Set rngToCopy = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        '~~> Remove any filters
        .AutoFilterMode = False

        rngToCopy.Copy

        '
        '~~> Rest of the Code
        '
    End With
End Sub

这篇关于复制列中的所有单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！