vba 每次运行具有不同行数和列数的宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41953413/
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
Running macro with a different number of rows and columns each time
提问by Isra Shaikh
So I have a tedious task of separating/filtering data and changing the format and making a new sheet.
因此，我有一项繁琐的任务，即分离/过滤数据并更改格式并制作新工作表。
My question is that if i record a macro for one sheet will it work for another keeping in mind that the number of rows and columns will ALWAYS change.
我的问题是，如果我为一张纸记录一个宏，它是否适用于另一张纸，记住行数和列数总是会改变。
There's no set number i would have to manually check that if i went into vba and changed the code accordingly.
没有设置编号，我必须手动检查是否进入 vba 并相应地更改了代码。
回答by Vityata
Depends on how do you record it. If you use ctrl+arrows in the recording you may be lucky. E.g. the following macro works on all rows, simply coloring them in yellow. independent, whether they are 4 or 400:
取决于你如何记录它。如果您在录制中使用 ctrl+箭头，您可能很幸运。例如，以下宏适用于所有行，只需将它们着色为黄色。独立的，无论是 4 还是 400：
Option Explicit
Sub Makro2()
'
' Makro2 Makro
'
'
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub
It is recorded with CTRL+Arrow, to select the last row. However, for more complicated code you should not use the recorder.
用CTRL+箭头记录，选择最后一行。但是，对于更复杂的代码，您不应该使用记录器。
回答by Mr.Burns
There is a few ways to get the last row/column in a spreedsheet
有几种方法可以获取电子表格中的最后一行/列
Lastrow = ActiveSheet.Range("A" & Activesheet.Rows.Count).End(xlup).row
LastCol = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).column
This will return the last row in column A, you may need to change this depending where your data starts. The LastColwill return a number for the column (Note that .Cellsgoes .Cells([Row Index], [Column Index])). This is the way I get the last row/column, I find it easier to understand and can do any manipulation needed.
这将返回 A 列中的最后一行，您可能需要根据数据的开始位置进行更改。该LastCol会返回一个数列（注意，.Cells去.Cells([Row Index], [Column Index])）。这是我获得最后一行/列的方式，我发现它更容易理解并且可以进行任何需要的操作。
LastRow = Cells.SpecialCells(xlLastCell).Row
LastColumn = Cells.SpecialCells(xlLastCell).Column
This is another way to get the last row/column, this does have issues as Excel workouts the last row/column in an odd way. If you edit the last cell in the last row (XFD1048576 in my case) then that becomes the .SpecialCells(xlLastCell)s last cell.
这是获取最后一行/列的另一种方法，这确实存在问题，因为 Excel 以一种奇怪的方式锻炼最后一行/列。如果您编辑最后一行中的最后一个单元格（在我的情况下为 XFD1048576），那么它就会成为.SpecialCells(xlLastCell)最后一个单元格。
Let me know if you need anything clarified
如果您需要澄清任何信息，请告诉我
回答by Durgaprasad
Recording will give you some basic idea of macro execution. But if you want that code need to work in all scenarios then you need to do some customization to your code.
录制将为您提供一些宏执行的基本概念。但是，如果您希望该代码需要在所有场景中都能正常工作，那么您需要对您的代码进行一些自定义。
To find the last row there are so many ways.
要找到最后一行，有很多方法。
Source : http://www.vbausefulcodes.in/usefulcodes/finding-last-row-in-excel-vba.php
来源：http: //www.vbausefulcodes.in/usefulcodes/finding-last-row-in-excel-vba.php
sub lastrow()
lastrow = Range("A" & Rows.Count).End(xlUp).Row
Msgbox lastrow
End sub
*************************************
Other ways
**************************************
Sub FindingLastRow()
'PURPOSE: Different ways to find the last row number of a range
Dim sht As Worksheet
Dim LastRow As Long
Set sht = ThisWorkbook.Worksheets("Sheet1")
'Ctrl + Shift + End
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
'Using UsedRange
sht.UsedRange 'Refresh UsedRange
LastRow = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
'Using Table Range
LastRow = sht.ListObjects("Table1").Range.Rows.Count
'Using Named Range
LastRow = sht.Range("MyNamedRange").Rows.Count
'Ctrl + Shift + Down (Range should be first cell in data set)
LastRow = sht.Range("A1").CurrentRegion.Rows.Count
End Sub
but you need to use the best method which matches with your scenario.
但您需要使用与您的场景相匹配的最佳方法。
