How to select excel cells range only alphabet followed?

2
votes

I am writing vba script which select cells range in excel sheet but it select in this format $G$1:$K$1 but i need only this format $G:$K eliminate number while selecting or any other method to eliminate number followed by $.

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
1
vbaexcel

1 Answers

3
votes

You can use the EntireColumn property of the Range object to get the columns

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

    Set Rng = rnge.EntireColumn

    MsgBox Rng.Address