Refer to entire column using variables-CodePudding

I have a find method below:

Sub Find_First()
    Dim FindString As String
    Dim Rng As Range

    FindString = Range("s4").Value

    If Trim(FindString) <> "" Then
        With Sheet3.Range("A:A") 'searches all of column A
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                'Application.Goto Rng, True 'value found
                MsgBox Rng.Address
            Else
                MsgBox "Nothing found" 'value not found
            End If
        End With
    End If
End Sub

but when I tried to change the range("a:a") to a variable it would give me an error. for example:

col = range ("a1").value    'a1 gives a,b,c,d or e

with Sheet3.Range(col:col) 'this would give me a compile error Expected : list separator or )

why is that?

CodePudding user response：

Instead of sheet.range you can use column, It should work; So try this;

Sub select_column()

col = Range("A1").Value
Columns(col).Select

End Sub

Hope this Helps...

CodePudding user response：

Sheet3.Range(col:col) needs to be Sheet3.Range(col & ":" & col) then.

Alternatively you can use Sheet3.Columns(col).