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

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只需输入列的编号，而不是字母

2 个解决方案

#1

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也是如此。

#2

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

#1