vba 将选择设置为范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15140786/
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
Setting Selection as Range
提问by mango
Can someone please hint at what i might be doing wrong here? For now I am effectively trying to do a Ctrl-A command to do a select all on a block of data in vba. Then i want that selection to be saved as a range, so that I can use it later.
有人可以暗示我在这里可能做错了什么吗？现在，我正在有效地尝试执行 Ctrl-A 命令来对 vba 中的数据块进行全选。然后我希望将该选择保存为一个范围，以便我以后可以使用它。
Dim rngAdData As Range
.....
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set rngAdData = Selection
Range(rngAdData).AdvancedFilter Action:=xlFilterInPla.... //<----
The last line gives me a run-time error '1004': Method 'Range' of object 'Global' failed
最后一行给了我一个 run-time error '1004': Method 'Range' of object 'Global' failed
When I do it the following way, it works
当我按照以下方式进行操作时，它会起作用
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).AdvancedFilter Action:=xlFilterInPla....
But doing it this way is cumbersome because I need to use that range again here
但是这样做很麻烦，因为我需要在这里再次使用该范围
With ActiveWorkbook.Worksheets("....").Sort
.SetRange Range(Selection) //<---
The line being pointed to gives me that same error.
指向的行给了我同样的错误。
采纳答案by Brad
Range(rngAdData)is feeding a range to a range. Just use rngAdData.AdvancedFilter
Range(rngAdData)正在向一个范围提供一个范围。只需使用rngAdData.AdvancedFilter
It's the same idea on your second problem. Use this syntax instead.
你的第二个问题也是同样的想法。请改用此语法。
With ActiveWorkbook.Worksheets("....").Sort
.SetRange Selection
With that said you should be using another means of getting your desired range other than using Selector Selectionstatements. Something like this should work better
话虽如此，您应该使用另一种方法来获得所需的范围，而不是使用SelectorSelection语句。像这样的东西应该工作得更好
Dim rngAdData As Range
Dim sht As Worksheet, bottomMostRow As Long, rightMostColumn As Long
Set sht = ActiveSheet
With sht
bottomMostRow = .Cells(1, 1).End(xlDown).Row
rightMostColumn = .Cells(1, 1).End(xlToRight).Column
Set rngAdData = .Range(.Cells(1, 1), .Cells(bottomMostRow, rightMostColumn))
End With
回答by Brad
Try below code :
试试下面的代码：
Sub sample()
Dim rng As Range
Range("A1").Select
Set rng = Range("A1").CurrentRegion
rng.AdvancedFilter xlFilterInPlace, Sheets("sheet1").Range("E7:H8"), False
End Sub
