vba - 如何选择Excel单元格范围仅跟随字母？

Question

我正在编写vba脚本，该脚本选择Excel工作表中的单元格范围，但它以这种格式选择$G$1:$K$1，但我只需要这种格式$G:$K在选择时消除数字或任何其他方法来消除数字随后是$。

Sub vloos()

Dim rnge As Range
Dim Rng As Range
Dim user As String
Dim file As String
file = "E:\output4.xls"
Workbooks.Add
ActiveWorkbook.SaveAs file
    Set nb = Application.Workbooks.Open(file)
    Set ns = nb.Worksheets("Sheet1")

    'get workbook path
    filename = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xls", Title:="Please select a file")


    'set our workbook and open it
    Set wb = Application.Workbooks.Open(filename)
    'set our worksheet
    Set ws = wb.Worksheets("Sheet1")

    'set the range for vlookup

On Error GoTo En
    Set rnge = Application.InputBox(Prompt:="Range to format:", Title:="Format Range", Type:=8)

    Set Rng = ws.Range(rnge.Address)

    MsgBox Rng.Address
En:
End Sub

Answer 1

您可以使用 Range 对象的 EntireColumn 属性来获取列

On Error GoTo En
    Set rnge = Application.InputBox(Prompt:="Range to format:", Title:="Format Range", Type:=8)

    Set Rng = rnge.EntireColumn

    MsgBox Rng.Address