Excel VBA,如何根据列中的数据选择行? [英] Excel VBA, How to select rows based on data in a column?
问题描述
Sub SelectAllReleventText()
/ pre>
Do While Range(A1)。Offset(1,6)<空
行(ActiveCell.Row)。选择
ActiveCell.Offset(1,0)。选择
循环
End Sub
这是我的脚本,我被告知它不会做什么,这是我预期的,因为这是我的第一次尝试。我想出了一个变量未定义的错误。我以为我定义了这个变量,但是我猜这个Excel VBA并不足够。
这是我试图做的。
- 在工作簿1中,在B6上有一个字母数字名称,我希望该行被选中。
- 下一行,如果有文字选择该行。
- 继续,直到文字不再普遍。
- 复制选定的行。
- 粘贴到另一个工作簿(Workbook2),进入标签1,从第2行开始,因为第1行有标题。 / li>
提前感谢。只是一个头脑,我正在使用VBA中的Options Explicit,因为我被告知这是正确的做事方式...
解决方案是使用
Option Explicit
是一个好习惯。使用。选择
但不是:)它降低了代码的速度。也完全证明了表格名称,代码将始终运行Activesheet
,这可能不是您实际想要的。
Option Explicit
子样本()
Dim lastRow As Long,i As Long
Dim CopyRange As Range
'~~>将Sheet1更改为相关表名称
带表格(Sheet1)
lastRow = .Range(A& .Rows.Count).End(xlUp).Row
对于i = 2 To lastRow
如果Len(Trim(.Range(A& i).Value))< 0然后
如果CopyRange不是,然后
设置CopyRange = .Rows(i)
Else
设置CopyRange = Union(CopyRange,.Rows(i))
结束如果
Else
退出
结束如果
下一个
如果没有CopyRange是没有
'~~>将Sheet2更改为相关工作表名称
CopyRange.Copy Sheets(Sheet2)。Rows(1)
End If
End With
End Sub
注意
第2行直到第10行和第11行为空白,然后您再次从第12行获取数据,则上述代码将仅将数据从第2行复制到第10行
如果要复制所有具有数据的行,然后使用此代码。
Option Explicit
子样本)
Dim lastRow As Long,i As Long
Dim CopyRange As Range
'~~>将Sheet1更改为相关表名称
带表格(Sheet1)
lastRow = .Range(A& .Rows.Count).End(xlUp).Row
对于i = 2 To lastRow
如果Len(Trim(.Range(A& i).Value))< 0然后
如果CopyRange不是,然后
设置CopyRange = .Rows(i)
Else
设置CopyRange = Union(CopyRange,.Rows(i))
结束如果
结束如果
下一个
如果没有CopyRange是没有
'~~>将Sheet2更改为相关工作表名称
CopyRange.Copy Sheets(Sheet2)。Rows(1)
End If
End With
End Sub
希望这是你想要的?
Sid
Sub SelectAllReleventText() Do While Range("A1").Offset(1, 6) <> Empty Rows(ActiveCell.Row).Select ActiveCell.Offset(1, 0).Select Loop End Sub
Here is my script, I've been told it doesn't do what it is meant to, which I expected since this was my first attempt. I am coming up with a variable not defined error. I thought I defined the variable, but I guess it wasn't specific enough for Excel VBA.
This is what I am attempting to do.
- In Workbook 1, On B6 there is an alphanumeric name, I want that row to be selected.
- Go down one row, if there is text there select that row.
- Continue till text is no longer prevalent.
- Copy selected rows.
- Paste into another workbook (Workbook2), into tab 1, starting on row 2, since row 1 has headers.
Thanks in advance. Just a heads up, I am using the Options Explicit in my VBA because I was told it was the "right way to do thing"...
解决方案Yes using
Option Explicit
is a good habit. Using.Select
however is not :) it reduces the speed of the code. Also fully justify sheet names else the code will always run for theActivesheet
which might not be what you actually wanted.Is this what you are trying?
Option Explicit Sub Sample() Dim lastRow As Long, i As Long Dim CopyRange As Range '~~> Change Sheet1 to relevant sheet name With Sheets("Sheet1") lastRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 2 To lastRow If Len(Trim(.Range("A" & i).Value)) <> 0 Then If CopyRange Is Nothing Then Set CopyRange = .Rows(i) Else Set CopyRange = Union(CopyRange, .Rows(i)) End If Else Exit For End If Next If Not CopyRange Is Nothing Then '~~> Change Sheet2 to relevant sheet name CopyRange.Copy Sheets("Sheet2").Rows(1) End If End With End Sub
NOTE
If if you have data from Row 2 till Row 10 and row 11 is blank and then you have data again from Row 12 then the above code will only copy data from Row 2 till Row 10
If you want to copy all rows which have data then use this code.
Option Explicit Sub Sample() Dim lastRow As Long, i As Long Dim CopyRange As Range '~~> Change Sheet1 to relevant sheet name With Sheets("Sheet1") lastRow = .Range("A" & .Rows.Count).End(xlUp).Row For i = 2 To lastRow If Len(Trim(.Range("A" & i).Value)) <> 0 Then If CopyRange Is Nothing Then Set CopyRange = .Rows(i) Else Set CopyRange = Union(CopyRange, .Rows(i)) End If End If Next If Not CopyRange Is Nothing Then '~~> Change Sheet2 to relevant sheet name CopyRange.Copy Sheets("Sheet2").Rows(1) End If End With End Sub
Hope this is what you wanted?
Sid
这篇关于Excel VBA,如何根据列中的数据选择行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!