将最后一列与指定行的数据复制到下一个空白列 [英] Copy last column with data on specified row to the next blank column

问题描述

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

有没有办法？

我已经设法使用行：

  lastrowSrc = Sheets（Overview）。Range B& Rows.Count）.End（xlUp）.Row 
  

然而，这使B12范围，使用columns.count只是放入列数，而不是字母

解决方案

要获得确切的列一个工作表，使用这个代码。

  Option Explicit 
 
子样本（）
 Dim ws As Worksheet 
 Dim LastCol As Long 
 
设置ws =表格（Sheet1）
 
'~~>此检查是必需的其他.FIND将给你一个空的表单错误
如果Application.WorksheetFunction.CountA（ws.Cells）= 0然后
 LastCol = 1 
 Else 
 LastCol = ws.Cells.Find（what：=*，_ 
之后：= ws.Range（A1），_ 
 Lookat：= xlPart，_ 
 LookIn：= xlFormulas ，_ 
 SearchOrder：= xlByColumns，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）.Column 
 End If 
 
 Debug.Print LastCol 
 End Sub 
  

---

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

  Option Explicit 
 
 Sub Sample（） 
 Dim ws As Worksheet 
 Dim LastCol As Long 
 Dim rng As Range 
 
设置ws =表格（Sheet1）
 
设置rng = ws.Cells.Find（什么：=*，_ 
之后：= ws.Range（A1），_ 
 Lookat：= xlPart，_ 
 LookIn：= xlFormulas，_ 
 SearchOrder：= xlByColumns，_ 
 SearchDirection：= xlPrevious，_ 
 MatchCase：= False）
 
如果rng不是，然后
 LastCol = 1 
 Else 
 LastCol = rng.Column 
如果
 
 Debug.Print LastCol 
 End Sub 
  

---

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

  Debug.Print ws.Cells（1，ws.Columns.Count）.End（xlToLeft）.Column 
  

ws是哟相关工作表。

类似于Row，请参阅这个。

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

解决方案

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

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

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

Where ws is your relevant worksheet.

Similarly for Row see this.

这篇关于将最后一列与指定行的数据复制到下一个空白列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！