时间戳

首页 > 解决方案 > Excel中范围的地址

excel - Excel中范围的地址

问题描述

我使用以下代码将选择的地址复制到变量（仅可见单元格），但最多只能复制 255 个字符。如果地址超过 255 个字符，则会受到限制。假设字符串变量可以是 63000 个字符长，不是吗？我该如何解决这个问题？非常感谢您提前。

编码：

Sel=Selection.SpecialCells(xlCellTypeVisible).Address(False, False)

变量内容：

C6:C10,C12:C13,C15:C27,C29:C31,C33:C54,C56:C64,C66,C68:C71,C73:C81,C83:C84,C86:C87,C89:C101,C103:C122,C124:C132,C135:C155,C158:C162,C164:C166,C168:C187,C189:C192,C194:C199,C201:C212,C214,C216:C233,C235:C251,C253:C262,C264:C267,C269:C270,C272:C292,C294

（251 个字符）

它应该是什么样子（例如）：

C6:C10,C12:C13,C15:C27,C29:C31,C33:C54,C56:C64,C66,C68:C71,C73:C81,C83:C84,C86:C87,C89:C101,C103:C122,C124:C132,C135:C155,C158:C162,C164:C166,C168:C187,C189:C192,C194:C199,C201:C212,C214,C216:C233,C235:C251,C253:C262,C264:C267,C269:C270,C272:C292,C294:C296,C299:C316

（266 个字符）

标签： excelvba

解决方案

考虑：

Sub adres()
    Dim r As Range, s As String, rr As Range
    Set r = Selection.Cells.SpecialCells(xlCellTypeVisible)
    s = ""

    For Each rr In r
        s = s & "," & rr.Address(0, 0)
    Next rr

    s = Mid(s, 2)
    MsgBox s
End Sub

Areas如果您想稍微“压缩”字符串，可以使用。

推荐阅读