选择在 Excel 宏中有值的列（VBA 中的范围对象）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10050946/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Selecting columns that have values in Excel Macro (range object in VBA)
提问by goodguys_activate
How do I modify this line in VBA to only select the columns that have values?
如何在 VBA 中修改此行以仅选择具有值的列？
Set rng = Range("A1", Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible)
I don't think I'm doing something right since the CountLargeproperty is several billion cells
我不认为我在做正确的事情，因为该CountLarge财产是数十亿个单元格
Here is a sample of my data
这是我的数据示例
回答by Siddharth Rout
@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
@SiddharthRout 是的，我只需要有数据的行。我想我现在可以使用来自@JMax 的 End(xlToLeft) ......现在我正在迭代单元格，一旦到达最后一行，我就可以退出 For each 循环。我现在可能有这个工作。– makerofthings7 14 分钟前
For this neither you need .SpecialCellsnor do you need to loop through the rows :)
为此，您既不需要.SpecialCells也不需要遍历行:)
Here is a sample code. This will copy all the rows which have data to Sheet2 (TRIED AND TESTED)
这是一个示例代码。这会将所有包含数据的行复制到 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: 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
@makerofthings7：我想我知道你到底想做什么:) 你不需要使用循环来实现你想要的。只是一个简单的问题。是否有可能说 Cell C10 可能有一个值而 B10 可能没有？– Siddharth Rout 12 分钟前
If there is then we will have to set the autofilter criteria accordingly.
如果有，那么我们将不得不相应地设置自动过滤条件。
Edit:WAY 2
编辑：方式2
The other way would be to sort your data, pushing the blanks way down and then copying the resulting range :)
另一种方法是对数据进行排序，将空白向下推，然后复制结果范围:)
HTH
HTH
