vba 将包含指定行数据的最后一列复制到下一个空白列

Copy last column with data on specified row to the next blank column

提问by K20GH

I have a spread sheet and I need to look for the last column that has data in it. Then I need to copy this column and copy it to the next blank column.

我有一个电子表格，我需要查找包含数据的最后一列。然后我需要复制此列并将其复制到下一个空白列。

Is there a way to do this?

有没有办法做到这一点？

I've managed to do it with rows using:

我已经设法使用以下行来做到这一点：

lastrowSrc = Sheets("Overview").Range("B" & Rows.Count).End(xlUp).Row

However this puts B12 in the range, using columns.count simply puts in the number of the column, not the letter

然而，这会将 B12 放在范围内，使用 columns.count 只是简单地放入列的编号，而不是字母

回答by Siddharth Rout

To get the exact column in a worksheet, use this code.

要获取工作表中的确切列，请使用此代码。

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastCol As Long

    Set ws = Sheets("Sheet1")

    '~~> This check is required else .FIND will give you error on an empty sheet
    If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
        LastCol = 1
    Else
        LastCol = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    End If

    Debug.Print LastCol
End Sub

---

---

EDIT: This is courtesy @brettdj. You can also use the range object to find the last column

编辑：这是礼貌@brettdj。您还可以使用范围对象查找最后一列

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim LastCol As Long
    Dim rng As Range

    Set ws = Sheets("Sheet1")

    Set rng = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False)

    If rng Is Nothing Then
        LastCol = 1
    Else
        LastCol = rng.Column
    End If

    Debug.Print LastCol
End Sub

---

---

To get the last column of a particular row, say row 1 use this

要获取特定行的最后一列，请说第 1 行使用此

    Debug.Print ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

Where ws is your relevant worksheet.

ws 是您的相关工作表。

Similarly for Row see this.

同样对于 Row 看到这个。

回答by Joel Stern

I found that some of the answers didn't work for my worksheet that had a few rows at the end that were shorter than the others in the worksheet. The code provided just gives the last column of the last row of the worksheet. Instead, I used a loop around code to find the last column in a row, using the Find example to get the last row in the workbook.

我发现有些答案对我的工作表不起作用，因为我的工作表最后有几行比工作表中的其他行短。提供的代码只给出工作表最后一行的最后一列。相反，我使用循环代码查找一行中的最后一列，使用 Find 示例获取工作簿中的最后一行。

Sub Sample()
    Dim ws As Worksheet
    Dim CurrRow, RowLastCol, LastRow, LastCol As Long

    Set ws = Sheets("Sheet1")

    '~~> This check is required else .FIND will give you error on an empty sheet
    If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
        LastCol = 1
    Else
        LastCol = 0
        LastRow = ws.Cells.Find(What:="*", _
                After:=ws.Range("A1"), _
                Lookat:=xlPart, _
                LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
        ' Loop through all the rows of the sheet saving off the highest column count
        For CurrRow = 1 to LastRow
            RowLastCol = ws.Cells(CurrRow, Columns.Count).End(xlToLeft).Column
            If RowLastCol > LastCol Then
                LastCol = RowLastCol
            End If
        Next CurrRow
    End If

    Debug.Print LastCol
End Sub