EXCEL VBA 在数组中存储/保存整行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11339876/
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
EXCEL VBA Store / Save entire row in an array?
提问by Andreas
Have recieved help how to search in a column for a string, and then store it in an array. Is it possible to store the entire Row? Have searched for it but can't find it.
已收到帮助如何在列中搜索字符串，然后将其存储在数组中。是否可以存储整个行？已经搜索过但找不到它。
I want to search in one Sheet that contains data by a string. Then copy those rows that contains that string to another sheet.
我想在一张包含字符串数据的工作表中进行搜索。然后将包含该字符串的那些行复制到另一个工作表。
My code looks like this.
我的代码看起来像这样。
Set wsRaw = Worksheets("raw_list")
Set phaseRange = wsRaw.Columns(PhaseCol)
SearchString = "start"
Set aCell = phaseRange.Find(What:=SearchString, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
Set bCell = aCell
ReDim Preserve arrStart(nS)
arrStart(nS) = aCell.Row
nS = nS + 1
Do While ExitLoop = False
Set aCell = phaseRange.FindNext(After:=aCell)
If Not aCell Is Nothing Then
If aCell.Row = bCell.Row Then Exit Do
ReDim Preserve arrStart(nS)
arrStart(nS) = aCell.Row
nS = nS + 1
Else
ExitLoop = True
End If
Loop
Else
End If
Thankfull for any help :)
感谢任何帮助:)
回答by Siddharth Rout
Since you are copying data from Sheet1 to Sheet2 based on your search criteria in a relevant column then then I would suggest using Autofilter.
由于您是根据相关列中的搜索条件将数据从 Sheet1 复制到 Sheet2，那么我建议使用 Autofilter。
See this
看到这个
Sub Sample()
Dim wsRaw As Worksheet
Dim strSearch As String
Dim PhaseCol As Long, LastRow As Long
Dim phaseRange As Range, rng As Range
strSearch = "start"
'~~> Change this to the relevant column
PhaseCol = 1
Set wsRaw = Sheets("raw_list")
With wsRaw
LastRow = .Range(Split(Cells(, PhaseCol).Address, "$")(1) & _
.Rows.Count).End(xlUp).Row
Set phaseRange = wsRaw.Range( _
Split(Cells(, PhaseCol).Address, "$")(1) & _
"1:" & _
Split(Cells(, PhaseCol).Address, "$")(1) & _
LastRow)
'~~> Remove any filters
.AutoFilterMode = False
'~~> Filter, offset(to exclude headers) and copy visible rows
With phaseRange
.AutoFilter Field:=1, Criteria1:=strSearch
Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
'~~> Chnage Sheet2 to the relevant sheet name where you want to copy
rng.Copy Sheets("Sheet2").Rows(1)
End With
'~~> Remove any filters
.AutoFilterMode = False
End With
End Sub
SNAPSHOT
快照
