选择在Excel宏中具有值的列（VBA中的范围对象） [英] Selecting columns that have values in Excel Macro (range object in VBA)

问题描述

如何在VBA中修改此行以仅选择具有值的列？

How do I modify this line in VBA to only select the columns that have values?

Set rng = Range("A1", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)

不要以为我正在做一些正确的事情，因为 CountLarge 属性是数十亿个单元格

I don't think I'm doing something right since the CountLarge property is several billion cells

这是一个示例的数据

推荐答案

@SiddharthRout是我只需要具有数据的行。我想我现在用@JMax的End（xlToLeft）工作了...现在我正在迭代单元格，只要到达最后一行就可以退出For Each循环。我现在可以工作 - makerofthings7 14分钟前

@SiddharthRout Yes I only need the rows that have data. I think I have it working now with End(xlToLeft) from @JMax ... Now that I'm iterating over the cells, I can just quit the For each loop once the last row is reached. I might have this working now. – makerofthings7 14 mins ago

code> .SpecialCells 也不需要循环遍历这些行：）

For this neither you need .SpecialCells nor do you need to loop through the rows :)

这是一个示例代码。这将把所有的数据行复制到Sheet2（ TRIED AND TESTED ）

Here is a sample code. This will copy all the rows which have data to Sheet2 (TRIED AND TESTED)

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim LastRow As Long, LastCol As Long

    Set ws = Sheets("Sheet1")

    With ws
        LastRow = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

        LastCol = .Cells.Find(What:="*", After:=.Range("A1"), Lookat:=xlPart, _
        LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
        MatchCase:=False).Column

        With .Range("A1:" & Split(Cells(, LastCol).Address, "$")(1) & LastRow)
            .AutoFilter Field:=1, Criteria1:="<>"
            Set rng = ws.AutoFilter.Range
            rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _
            Destination:=Sheets("Sheet2").Range("A1")
        End With
    End With
End Sub

SNAPSHOT

我假设特定行中的所有单元格都将具有数据，并且不会有这样的情况

I am assuming that all cells in a particular row will have data and there won't be a case like this

@ makerofthings7：我想我知道你正在尝试做什么:)你不需要使用循环来实现你想要的。只是一个快速的问题。有可能说，Cell C10可能有一个值，但B10可能不是？ - Siddharth Rout 12分钟前

@makerofthings7: I think I know what exactly you are trying to do :) you don't need to use loops to achieve what you want. Just a quick question. Is it possible that say Cell C10 might have a value but B10 might not? – Siddharth Rout 12 mins ago

如果有的话

编辑：
WAY 2

另一种方式是对数据进行排序，将空白方式向下推，然后复制所得范围：）

The other way would be to sort your data, pushing the blanks way down and then copying the resulting range :)

HTH

这篇关于选择在Excel宏中具有值的列（VBA中的范围对象）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！