Excel - 使用地址复制整列（包括空格）

发布于 2024-11-13 10:52:47 字数 727 浏览 4 评论 0原文

我想做的是扫描一张纸上的每个标题（标题位于“1:1”），找到一个名为“经销商代码”的标题，然后将整个列复制到另一张纸上。

我当前的问题是“经销商代码”列中有空白，因此选择停止。我将从工作表底部向上工作以避免这种情况，但由于标题单元格存储在地址中，我无法提取列字母。

有没有一种方法可以在我的选择中包含空白，或者提取列字母并向上工作？

如果我处理这个问题非常不方便，请告诉我！我想尽可能多地学习。

下面是我的代码。帮助将不胜感激。

'Copies over Dealer Codes
With Sheets("Raw Data").Range("1:1")
Set c = .Find("Dealer Code", LookIn:=xlValues)
If Not c Is Nothing Then
    firstAddress = c.Address
    Do
        Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
End If

Sheets("Raw Data").Range(firstAddress).Select
Sheets("Raw Data").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy (Sheets("Copied Data").Range("A1"))
End With

原文

What I'm trying to do is scan over every header on one sheet (the headers are located in "1:1"), find a header called "Dealer Code", then copy that entire column to another sheet.

My current problem is that there are blanks in the Dealer Code column so the selection stops. I would work from the bottom of the sheet upward to avoid this, but since the header cell is stored in an address, I can't extract the column letter.

There a way I can include blanks in my selection, or extract the column letter and work upwards?

If I'm approaching the problem very inconveniently, please let me know! I want to learn as much as possible.

Below is my code. Help would be greatly appreciated.

'Copies over Dealer Codes
With Sheets("Raw Data").Range("1:1")
Set c = .Find("Dealer Code", LookIn:=xlValues)
If Not c Is Nothing Then
    firstAddress = c.Address
    Do
        Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
End If

Sheets("Raw Data").Range(firstAddress).Select
Sheets("Raw Data").Range(Selection, Selection.End(xlDown)).Select
Selection.Copy (Sheets("Copied Data").Range("A1"))
End With

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一枫情书 2024-11-20 10:52:47

尝试这样的操作：

Sub Test()
    With ActiveSheet.Range("1:1")
    Set c = .Find("Dealer Code")
    Dim column As String
    column = Mid(c.Address, 2, 1)
    Range(column & ":" & column).Select
    End With
End Sub

这会选择整个列，但可以轻松更改为选择从第二行到末尾的元素。由于您有空白，类似 Range(column & "2:" & column & Range(column & "65536").End(xlUp).Row).Select 的方法可以工作，但是我承认有点笨拙。

其他解决方案可能更优化。

Try something like this:

Sub Test()
    With ActiveSheet.Range("1:1")
    Set c = .Find("Dealer Code")
    Dim column As String
    column = Mid(c.Address, 2, 1)
    Range(column & ":" & column).Select
    End With
End Sub

This selects the whole column, but is easily changed to select the elements from the 2nd row down to the end. Since you have blanks, something like Range(column & "2:" & column & Range(column & "65536").End(xlUp).Row).Select works, but is a bit kludgey, I'll admit.