我需要一个 VBA 代码来计算从 ss 到 ss 的行数，返回该数字并复制并粘贴该行和所有其他列

I need a VBA code to count the number rows, which varies from ss to ss, return that number and copy and paste that row and all other columns

提问by Tony Curado

I have vba question I have been trying to find the answer for for a long time. I have numerous spreadsheets from numerous clients that I run macro's on, I'm new to coding and have been able to mostly figure out what I need to do. My clients send us data monthly and every month the number of rows change. The columns don't change but the amount of data does. My previous macro's I have just chosen the entire column to copy and paste onto our companies template. This worked fine for must things but has created some really long code and macros take a long time. I would like to write a code that counts how many rows are in a certain column and then from there copies and pastes that however many rows it counted in each column. Only a few columns contain data in every row, so I need it to count the rows in one specific column and apply to that every column. Any help would be appreciated. Thanks Tony

我有 vba 问题，我一直在努力寻找答案。我有许多来自我运行宏的客户的大量电子表格，我是编码新手，并且能够大部分时间弄清楚我需要做什么。我的客户每月向我们发送数据，每个月的行数都会发生变化。列不会改变，但数据量会改变。我之前的宏我刚刚选择了整个列复制并粘贴到我们公司的模板上。这对于必须的事情来说效果很好，但是创建了一些非常长的代码，并且宏需要很长时间。我想编写一个代码来计算某一列中有多少行，然后从那里复制和粘贴它在每列中计算了多少行。每行只有几列包含数据，所以我需要它来计算一个特定列中的行数并应用于每一列。任何帮助，将不胜感激。谢谢托尼

Hi Guys, Still having issues with this, below I pasted the code I'm using if anyone can see why it won't run please help.

嗨，伙计们，仍然有问题，下面我粘贴了我正在使用的代码，如果有人能看到它为什么无法运行，请帮忙。

Windows("mmuworking2.xlsx").Activate
 Workbooks.Open Filename:= _
        "C:\Users\I53014\Desktop\QC DOCS\Sample_Data_Import_Template.xlsx"
 Windows("mmuworking2.xlsx").Activate
    Dim COL As Integer
    COL = Range("A:DB").Columns.Select
    **Range(Cells(2, COL), Cells(Range("E" & Rows.Count).End(xlUp).Row, COL)).Copy Destination:=Windows("Sample_Data_Import_Template.xlsx").Range("A2")**
    Range("A2").Paste
    Range("A5000").Formula = "='C:\Users\I53014\Desktop\[Import_Creator.xlsm]sheet1'!$B"
    ActiveWorkbook.SaveAs Filename:="Range (A5000)", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

I bolded where it keeps stopping.

我在它不断停止的地方加粗。

回答by Ripster

This should give you the last row containing data:

这应该给你包含数据的最后一行：

ActiveSheet.UsedRange.Rows.Count

This will give you the last row in a specific column:

这将为您提供特定列中的最后一行：

Range("B" & Rows.Count).End(xlUp).Row

here is an example of how I can copy every row in the first three columns of a worksheet

这是我如何复制工作表前三列中的每一行的示例

Sub Example()
    Dim LastRow As Long

    LastRow = ActiveSheet.UsedRange.Rows.Count
    Range(Cells(1, 1), Cells(LastRow, 3)).Copy Destination:=Sheet2.Range("A1")
End Sub

You have to be careful as there are some caveats to both methods.

您必须小心，因为这两种方法都有一些注意事项。

ActiveSheet.UsedRangemay include cells that do not have any data if the cells were not cleaned up properly.

ActiveSheet.UsedRange如果未正确清理单元格，则可能包括没有任何数据的单元格。

Range("A" & Rows.Count).End(xlUp).Rowwill only return the number of rows in the specified column.

Range("A" & Rows.Count).End(xlUp).Row只会返回指定列中的行数。

Rows(Rows.Count).End(xlUp).Rowwill only return the number of rows in the first column.

Rows(Rows.Count).End(xlUp).Row只会返回第一列中的行数。

EditAdded an example
Edit2Changed the example to be a bit more clear

编辑添加了一个示例
Edit2将示例更改为更清晰一点

For this example lets say we have this data

对于这个例子，假设我们有这个数据

You could copy any other column down to the number of rows in column A using this method:

您可以使用此方法将任何其他列复制到 A 列中的行数：

Sub Example()
    Dim Col as Integer
    Col = Columns("C:C").Column

    'This would copy all data from C1 to C5
    'Cells(1, Col) = Cell C1, because C1 is row 1 column 3
    Range(Cells(1, Col), Cells(Range("A" & Rows.Count).End(xlUp).Row, Col)).Copy Destination:=Sheet2.Range("A1")
End Sub

The end result would be this:

最终结果是这样的：