[已解决]【求助】对批量选取整行的续问

xdwy81129

原题：[已解决]变量选择整行的问题.

http://www.excelpx.com/forum.php?mod=viewthread&tid=99445

内容：工作表的A列有数值9,找到这几个9且选择整行,用V实现.

解答：

Sub test()
    Dim Ro As Integer
    Dim Rng As Range
    Dim RngCount As Integer
    For Ro = 1 To [A65536].End(xlUp).Row
        If Cells(Ro, 1).Value = 9 Then
            RngCount = RngCount + 1
            If RngCount = 1 Then
                Set Rng = Cells(Ro, 1)
            Else
                Set Rng = Union(Rng, Cells(Ro, 1))
            End If
        End If
    Next
    Rng.EntireRow.Select
    Set Rng = Nothing
End Sub

请老师指教的是：不是选择整行，而是都选择第1到6列

或者是

选择第1到6列及第9到16列怎么改变上面的答案（这里是同时选择）？？

[此贴子已经被作者于2009-11-10 19:06:00编辑过]

最佳答案

月排行榜 / 总排行榜

搁浅2008

2009-11-10 19:21

Sub aa()
    Dim Ro As Integer
    Dim Rng As Range
    Dim RngCount As Integer
    For Ro = 1 To [A65536].End(xlUp).Row
        If Cells(Ro, 1).Value = 9 Then
            RngCount = RngCount + 1
            If RngCount = 1 Then
                Set Rng = Union(Range(Cells(Ro, 1), Cells(Ro, 6)), Range(Cells(Ro, 9), Cells(Ro, 16)))
            Else
                Set Rng = Union(Rng, Range(Cells(Ro, 1), Cells(Ro, 6)))
                Set Rng = Union(Rng, Range(Cells(Ro, 9), Cells(Ro, 16)))
            End If
        End If
    Next
    Rng.Select
    Set Rng = Nothing
End Sub

跳转到最佳答案楼层

搁浅2008

把Set Rng = Union(Rng, Cells(Ro, 1))改成

 Set Rng = Union(Rng, range(Cells(Ro, 1),cells(ro,6))

Set Rng = Union(Rng, range(Cells(Ro, 9),cells(ro,16))

上边一句替换成下边2句试试

把Rng.EntireRow.Select

改成Rng.Select

xdwy81129

QUOTE:

以下是引用搁浅2008在2009-11-10 17:49:00的发言：

把Set Rng = Union(Rng, Cells(Ro, 1))改成

 Set Rng = Union(Rng, range(Cells(Ro, 1),cells(ro,6))

Set Rng = Union(Rng, range(Cells(Ro, 9),cells(ro,16))

上边一句替换成下边2句试试

把Rng.EntireRow.Select

改成Rng.Select

怎么写上去这2句出现为红色字符呢？？

提示：语法错误

提示：缺少列表分隔符或）

    Dim Ro As Integer
    Dim Rng As Range
    Dim RngCount As Integer
    For Ro = 1 To [A65536].End(xlUp).Row
        If Cells(Ro, 1).Value = 9 Then
            RngCount = RngCount + 1
            If RngCount = 1 Then
                Set Rng = Cells(Ro, 1)
            Else
Set Rng = Union(Rng,range(Cells(Ro,1),cells(ro,6))
Set Rng = Union(Rng,range(Cells(Ro,9),cells(ro,16))
            End If
        End If
    Next
    Rng.Select
    Set Rng = Nothing
End Sub

搁浅2008

Sub aa()
    Dim Ro As Integer
    Dim Rng As Range
    Dim RngCount As Integer
    For Ro = 1 To [A65536].End(xlUp).Row
        If Cells(Ro, 1).Value = 9 Then
            RngCount = RngCount + 1
            If RngCount = 1 Then
                Set Rng = Union(Range(Cells(Ro, 1), Cells(Ro, 6)), Range(Cells(Ro, 9), Cells(Ro, 16)))
            Else
                Set Rng = Union(Rng, Range(Cells(Ro, 1), Cells(Ro, 6)))
                Set Rng = Union(Rng, Range(Cells(Ro, 9), Cells(Ro, 16)))
            End If
        End If
    Next
    Rng.Select
    Set Rng = Nothing
End Sub

xdwy81129

QUOTE:

以下是引用搁浅2008在2009-11-10 19:21:00的发言：
Sub aa()
    Dim Ro As Integer
    Dim Rng As Range
    Dim RngCount As Integer
    For Ro = 1 To [A65536].End(xlUp).Row
        If Cells(Ro, 1).Value = 9 Then
            RngCount = RngCount + 1
            If RngCount = 1 Then
                Set Rng = Union(Range(Cells(Ro, 1), Cells(Ro, 6)), Range(Cells(Ro, 9), Cells(Ro, 16)))
            Else
                Set Rng = Union(Rng, Range(Cells(Ro, 1), Cells(Ro, 6)))
                Set Rng = Union(Rng, Range(Cells(Ro, 9), Cells(Ro, 16)))
            End If
        End If
    Next
    Rng.Select
    Set Rng = Nothing
End Sub

感觉原来或者上面的命令式可以简短的。

应该一句if   then  结果就行了

怎么还有个ELSE来做判断有2种选择结果呢？？？？？？？？？？

搁浅2008

我只是比着上一个改的 我没有试验行不行[em04]

搁浅2008

 Set Rng = Union(Rng, Range(Cells(Ro, 1), Cells(Ro, 6)))

这里的rng要有一个初始值所以才要加的

xdwy81129

QUOTE:

以下是引用搁浅2008在2009-11-10 19:31:00的发言：
我只是比着上一个改的 我没有试验行不行[em04]

谢谢，找DJ问问

------dj没有在[em06]

[此贴子已经被作者于2009-11-10 19:40:02编辑过]

xdwy81129

QUOTE:

以下是引用搁浅2008在2009-11-10 19:33:00的发言：

 Set Rng = Union(Rng, Range(Cells(Ro, 1), Cells(Ro, 6)))

这里的rng要有一个初始值所以才要加的

可以想个方法，把初始值与后面的都找到，然后一次选取不就好了，就没有else，命令也简短了，搁浅老师帮帮忙了。

saler111

Sub test()
   j = 1
  For i = 1 To [A65536].End(xlUp).Row
         If Sheets("Sheet2").Cells(i, 1).Value = 9 Then
          Sheets("Sheet1").Cells(j, 1).Resize(1, 6).Value = Sheets("Sheet2").Cells(i, 1).Resize(1, 6).Value
           Sheets("Sheet1").Cells(j, 9).Resize(1, 2).Value = Sheets("Sheet2").Cells(i, 9).Resize(1, 2).Value
           j = j + 1
           End If
    Next
End Sub