blocks|key|2793264|text|您可以简单地使用cells.select选择工作表中的所有单元格。你可以通过说Range(Cells.Address)来获取一个有效的地址。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2793265|如果您想要查找在其中进行了一些格式更改或输入值的最后一个Used+Range，您可以调用ActiveSheet.UsedRange并从那里选择它。希望这能有所帮助|2793266|entityMap^0|8|C|13|K|0|S|A|18|L|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|I|$]]

You can simply use <code>cells.select</code> to select all cells in the worksheet. You can get a valid address by saying <code>Range(Cells.Address)</code>. 

If you want to find the last <code>Used Range</code> where you have made some formatting change or entered a value in you can call <code>ActiveSheet.UsedRange</code> and select it from there. Hope that helps

blocks|key|3005808|text|我相信你想要找到A1的当前区域和周围的单元格-不一定是工作表上的所有单元格。如果是这样的话-只需使用...Range("A1").CurrentRegion|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3005809|entityMap^0|1H|P|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

I believe you want to find the current region of A1 and surrounding cells - not necessarily all cells on the sheet.
If so - simply use...
Range("A1").CurrentRegion

blocks|key|180908|text|我建议你录制一个宏，就像在这篇文章中找到的那样；|type|unstyled|depth|inlineStyleRanges|entityRanges|data|180909|Excel+VBA+macro+to+filter+records|offset|length|180910|但是如果你想找到你的数据的结尾而不是工作簿的结尾，如果你的数据的开头和结尾之间没有空单元格，我经常使用这样的东西；|180911|R+=+1
Do+While+Not+IsEmpty(Sheets("Sheet1").Cells(R,+1))
++++R+=+R+%2B+1
Loop
Range("A5:A"+&+R).Select+'This+will+give+you+a+specific+selection|code-block|syntax|javascript|180912|在数据结束后，R=为行数。这也可以用于列，如果您将C作为列表示，则可以使用类似于Cells(C，R).Select的内容。|180913|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/6469228/excel-vba-macro-to-filter-records^0|0|0|X|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@$D|Y|E|Z|1|10]]|A|$]]|$1|F|3|G|5|6|7|11|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|12|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

I would recommend recording a macro, like found in this post;

<a href="https://stackoverflow.com/questions/6469228/excel-vba-macro-to-filter-records">Excel VBA macro to filter records</a> 

But if you are looking to find the end of your data and not the end of the workbook necessary, if there are not empty cells between the beginning and end of your data, I often use something like this;

<pre><code>R = 1
Do While Not IsEmpty(Sheets("Sheet1").Cells(R, 1))
 R = R + 1
Loop
Range("A5:A" &amp; R).Select 'This will give you a specific selection
</code></pre>

You are left with R = to the number of the row after your data ends. This could be used for the column as well, and then you could use something like Cells(C , R).Select, if you made C the column representation.

blocks|key|2793360|text|这里有几个选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2793361|2793362|使用UsedRange属性|ordered-list-item|2793363|查找最后使用的行和列|2793364|使用模拟的shift+down和shift+right|2793365|2793366|我个人大部分时间都使用使用的范围和查找最后一行和列的方法。|2793367|下面是如何使用UsedRange属性来完成此操作：|2793368|Sheets("Sheet_Name").UsedRange.Select|code-block|syntax|javascript|2793369|此语句将选择工作表中所有使用的区域，请注意，当您删除列和行时，此语句有时不能很好地工作。|2793370|另一种方法是查找工作表中使用的最后一个单元格|2793371|Dim+rngTemp+As+Range
Set+rngTemp+=+Cells.Find("*",+SearchOrder:=xlByRows,+SearchDirection:=xlPrevious)
If+Not+rngTemp+Is+Nothing+Then
++++Range(Cells(1,+1),+rngTemp).Select
End+If|2793372|这段代码在做什么：|2793373|2793374|查找包含任何值的最后一个单元格|2793375|选择单元格(1，1)一直到最后一个单元格|2793376|2793377|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1B|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|1C|8|@]|9|@]|A|$]]|$1|H|3|I|5|E|7|1D|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1F|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1G|8|@]|9|@]|A|$]]|$1|O|3|P|5|Q|7|1H|8|@]|9|@]|A|$R|S]]|$1|T|3|U|5|6|7|1I|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|1J|8|@]|9|@]|A|$]]|$1|X|3|Y|5|Q|7|1K|8|@]|9|@]|A|$R|S]]|$1|Z|3|10|5|6|7|1L|8|@]|9|@]|A|$]]|$1|11|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]|$1|12|3|13|5|E|7|1N|8|@]|9|@]|A|$]]|$1|14|3|15|5|E|7|1O|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|17|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|18|$]]

you have a few options here:

<ol>
<li>Using the UsedRange property</li>
<li>find the last row and column used</li>
<li>use a mimic of shift down and shift right</li>
</ol>

I personally use the Used Range and find last row and column method most of the time.

Here's how you would do it using the UsedRange property:

<pre><code>Sheets("Sheet_Name").UsedRange.Select
</code></pre>

This statement will select all used ranges in the worksheet, note that sometimes this doesn't work very well when you delete columns and rows.

The alternative is to find the very last cell used in the worksheet

<pre><code>Dim rngTemp As Range
Set rngTemp = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rngTemp Is Nothing Then
 Range(Cells(1, 1), rngTemp).Select
End If
</code></pre>

What this code is doing:

<ol>
<li>Find the last cell containing any value</li>
<li>select cell(1,1) all the way to the last cell</li>
</ol>

blocks|key|183543|text|也许这可能会起作用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|183544|Sh.Range("A1"，Sh.Range("A“&+Rows.Count).End(xlUp))|183545|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Maybe this might work:

Sh.Range("A1", Sh.Range("A" &amp; Rows.Count).End(xlUp))

blocks|key|180944|text|您可以将所有单元格用作对象，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|180945|Dim+x+as+Range
Set+x+=+Worksheets("Sheet+name").Cells|code-block|syntax|javascript|180946|X现在是一个包含整个工作表的range对象|180947|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

you can use all cells as a object like this : 

<pre><code>Dim x as Range
Set x = Worksheets("Sheet name").Cells
</code></pre>

X is now a range object that contains the entire worksheet

blocks|key|181941|text|关于第一个问题，我正在研究同样的问题。记录宏时，我得到的结果是，首先选择单元格A76：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|181942|Sub+find_last_row()
++++Range("A76").Select
++++Range(Selection,+Selection.End(xlDown)).Select
End+Sub|code-block|syntax|javascript|181943|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Refering to the very first question, I am looking into the same.
The result I get, recording a macro, is, starting by selecting cell A76:

<pre><code>Sub find_last_row()
 Range("A76").Select
 Range(Selection, Selection.End(xlDown)).Select
End Sub
</code></pre>

blocks|key|181947|text|Sub+SelectAllCellsInSheet(SheetName+As+String)
++++lastCol+=+Sheets(SheetName).Range("a1").End(xlToRight).Column
++++Lastrow+=+Sheets(SheetName).Cells(1,+1).End(xlDown).Row
++++Sheets(SheetName).Range("A1",+Sheets(SheetName).Cells(Lastrow,+lastCol)).Select
End+Sub|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|181948|与ActiveSheet一起使用的：|unstyled|offset|length|style|BOLD|181949|Call+SelectAllCellsInSheet(ActiveSheet.Name)|181950|entityMap^0|0|H|1|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@$G|Q|H|R|I|J]]|9|@]|A|$]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$B|C]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>Sub SelectAllCellsInSheet(SheetName As String)
 lastCol = Sheets(SheetName).Range("a1").End(xlToRight).Column
 Lastrow = Sheets(SheetName).Cells(1, 1).End(xlDown).Row
 Sheets(SheetName).Range("A1", Sheets(SheetName).Cells(Lastrow, lastCol)).Select
End Sub
</code></pre>

To use with ActiveSheet:

<pre><code>Call SelectAllCellsInSheet(ActiveSheet.Name)
</code></pre>

blocks|key|179506|text|这是我使用的，我知道它可能需要一些完善，但我认为它会对其他人有所帮助…|type|unstyled|depth|inlineStyleRanges|entityRanges|data|179507|''STYLING''

Dim+sheet+As+Range

'+Find+Number+of+rows+used
Dim+Final+As+Variant
++++Final+=+Range("A1").End(xlDown).Row

'+Find+Last+Column
Dim+lCol+As+Long
++++lCol+=+Cells(1,+Columns.Count).End(xlToLeft).Column

Set+sheet+=+ActiveWorkbook.ActiveSheet.Range("A"+&+Final+&+"",+Cells(1,+lCol+))
With+sheet
++++.Interior.ColorIndex+=+1
End+With|code-block|syntax|javascript|179508|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is what I used, I know it could use some perfecting, but I think it will help others...

<pre><code>''STYLING''

Dim sheet As Range

' Find Number of rows used
Dim Final As Variant
 Final = Range("A1").End(xlDown).Row

' Find Last Column
Dim lCol As Long
 lCol = Cells(1, Columns.Count).End(xlToLeft).Column

Set sheet = ActiveWorkbook.ActiveSheet.Range("A" &amp; Final &amp; "", Cells(1, lCol ))
With sheet
 .Interior.ColorIndex = 1
End With
</code></pre>

blocks|key|181960|text|只要数据是连续的，选择范围内所有单元格的另一种方法是使用Range("A1",+Range("A1").End(xlDown).End(xlToRight)).Select。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|181961|entityMap^0|S|1M|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Another way to select all cells within a range, as long as the data is contiguous, is to use <code>Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select</code>.

blocks|key|3006140|text|我发现工作表".UsedRange“方法在许多情况下是解决这个问题的更好的方法。我遇到了一个截断问题，这是".CurrentRegion“方法的正常行为。当工作表由一列组成，而行中有空格(并且需要空格)时，使用Worksheets("Sheet1").Range("A1").CurrentRegion不会产生我想要的结果。在这种情况下，".CurrentRegion“将在第一条记录处截断。我实现了一个变通方法，但最近发现了一个更好的变通方法；请参阅下面的代码，它允许将整个集合复制到另一个工作表或识别实际地址(或仅行和列)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3006141|Sub+mytest_GetAllUsedCells_in_Worksheet()
++++Dim+myRange

++++Set+myRange+=+Worksheets("Sheet1").UsedRange
++++'Alternative+code:++set+myRange+=+activesheet.UsedRange

+++'use+msgbox+or+debug.print+to+show+the+address+range+and+counts
+++MsgBox+myRange.Address++++++
+++MsgBox+myRange.Columns.Count
+++MsgBox+myRange.Rows.Count

++'Copy+the+Range+of+data+to+another+sheet
++'Note:+contains+all+the+cells+with+that+are+non-empty
+++myRange.Copy+(Worksheets("Sheet2").Range("A1"))
+++'Note:++transfers+all+cells+starting+at+"A1"+location.++
+++'+++++++You+can+transfer+to+another+area+of+the+2nd+sheet
+++'+++++++by+using+an+alternate+starting+location+like+"C5".

End+Sub|code-block|syntax|javascript|3006142|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I have found that the Worksheet ".UsedRange" method is superior in many instances to solve this problem.
I struggled with a truncation issue that is a normal behaviour of the ".CurrentRegion" method. Using [ Worksheets("Sheet1").Range("A1").CurrentRegion ] does not yield the results I desired when the worksheet consists of one column with blanks in the rows (and the blanks are wanted). In this case, the ".CurrentRegion" will truncate at the first record. I implemented a work around but recently found an even better one; see code below that allows copying the whole set to another sheet or to identify the actual address (or just rows and columns):

<pre><code>Sub mytest_GetAllUsedCells_in_Worksheet()
 Dim myRange

 Set myRange = Worksheets("Sheet1").UsedRange
 'Alternative code: set myRange = activesheet.UsedRange

 'use msgbox or debug.print to show the address range and counts
 MsgBox myRange.Address 
 MsgBox myRange.Columns.Count
 MsgBox myRange.Rows.Count

 'Copy the Range of data to another sheet
 'Note: contains all the cells with that are non-empty
 myRange.Copy (Worksheets("Sheet2").Range("A1"))
 'Note: transfers all cells starting at "A1" location. 
 ' You can transfer to another area of the 2nd sheet
 ' by using an alternate starting location like "C5".

End Sub
</code></pre>

I found a similar solution to this question in c# <a href="https://stackoverflow.com/questions/4850738/how-to-select-the-whole-excel-sheet-in-excel-range-object-of-c">How to Select all the cells in a worksheet in Excel.Range object of c#?</a>

What is the process to do this in VBA?

I select data normally by using "ctrl+shift over arrow, down arrow" to select an entire range of cells. When I run this in a macro it codes out A1:Q398247930, for example. I need it to just be

<pre><code>.SetRange Range("A1:whenever I run out of rows and columns")
</code></pre>

I could easily do it myself without a macro, but I'm trying to make the entire process a macro, and this is just a piece of it. 

<pre><code>Sub sort()
 'sort Macro
 Range("B2").Select
 ActiveWorkbook.Worksheets("Master").sort.SortFields.Clear
 ActiveWorkbook.Worksheets("Master").sort.SortFields.Add Key:=Range("B2"), _
 SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Master").sort
 .SetRange Range("A1:whenever I run out of rows and columns")
 .Header = xlNo
 .MatchCase = False
 .Orientation = xlTopToBottom
 .SortMethod = xlPinYin
 .Apply
 End With
End Sub
</code></pre>

edit: 
There are other parts where I might want to use the same code but the range is say "C3:End of rows &amp; columns". Is there a way in VBA to get the location of the last cell in the document?

How do you select the entire excel sheet with Range using VBA?

我在c# 中找到了这个问题的类似解决方案。在VBA中执行此操作的过程是什么？我通常通过使用"ctrl+shift over arrow，down arrow“来选择整个单元格区域来选择数据。例如，当我在宏中运行它时，它会输出A1:Q398247930。我需要它只是.SetRange Range("A1:whenever...

问如何使用VBA选择整个带范围的excel工作表？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用VBA选择整个带范围的excel工作表？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用VBA选择整个带范围的excel工作表？
EN