如何设置最后一个数据行的Excel宏 [英] How to set the last data row excel macro

问题描述

我正在尝试创建一个宏来帮助我格式化某些文件，但是每个文件具有不同的行数(但始终具有相同的列数).我将最后一个范围定义为 99999 ，因为我不知道如何使宏识别包含某些数据的最后一行并在此停止.

I'm trying to make a macro to help me in format some files, but each file has different number of rows (but always same number of columns). I defined the last range as 99999 because i don't know how to make the macro recognize the last row with some data and stop there.

有人可以帮忙吗?

谢谢

  Rows("1:26").Select
    Selection.Cut
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Range("C16").Select
    Sheets("teste").Select
    Selection.Delete Shift:=xlUp
    Range("I3:N99999").Select
    Selection.Cut
    Range("I1").Select
    ActiveSheet.Paste
    Range("P3:P99999").Select

也尝试并取得了成功:

    Dim Nb_Rows As Integer
Nb_Rows = WorksheetFunction.CountA(Range("H:H"))
For i = 1 To Nb_Rows
Range("I" & i).Value = Range("H" & i).Value + Range("F" & i).Value
Next i

推荐答案

您可以使用类似的方法找到最后一行

You can use something like this to find the last row

lastRow = Range("N" & Rows.Count).End(xlUp).Row

然后使用字符串污染在范围对象中使用lastRow:

Then use string contatenation to use lastRow in the range object:

rangeIN = Range("I3:N" & lastRow)

这是我用于此示例程序的代码:

Here is the code I used for this sample program:

Sub SelectRange()
    Dim rangeIN As Range
    lastRow = Range("N" & Rows.Count).End(xlUp).Row
    Set rangeIN = Range("I3:N" & lastRow)
    rangeIN.Select
End Sub

您将要使用的确切代码取决于情况.例如，如果您知道N列中始终至少有一行数据，如果最后一行可能位于另一列中(如M)，依此类推，那么这取决于您将拥有的一些可变因素测试以使您的宏可靠地工作.我建议您查看此问题以了解更多信息信息，如Stax所建议.

What exact code you will want to use though depends on the circumstances. For instance, if you know that there will always be at least one line of data in the N column, if the last row may be in another column (like M), etc. So it depends on a few variable factors which you will have to test out to make your macro work reliably. I recommend checking out this question for more info, as suggested by Stax.

P.S.您无需选择要复制和粘贴的范围.这是一个常见的误解，并且会增加效率.通常，您可以改用以下方式:

P.S. you don't need to select a range to copy and paste it. This is a common misconception and adds inefficiency. You can usually use something like this instead:

Sheet2.Range("A1:B2").Value2 = Sheet1.Range("A1:B2").Value2

这篇关于如何设置最后一个数据行的Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！