how to select a column

[dd*]

2 questions:

I perform a search.
in the current active cell, I would like to select that column.
how do I select the whole column?


whats happening is its selecting the entire spreadsheets data

any ideas?

many thanks

dd
ps. you would think this was easy

 

[AlphaFrog]

This is my working code to select a column.
although i feel i need an if statement - if searchText found etc...

    Dim searchText As String
    Dim Found As Range
    
    searchText = "GL Code"
    
    Set Found = Cells.Find(What:=searchText, LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
    
    If Not Found Is Nothing Then
        'Match Found
        If Found.Column > 1 Then
            Application.ScreenUpdating = False
            Range("A:A").Insert Shift:=xlToLeft 'insert column A
            Found.EntireColumn.Copy Destination:=Columns("A:A")
            Found.EntireColumn.Delete
            Application.ScreenUpdating = True
        End If
    Else
        MsgBox "No match found for '" & searchText & "'.", , "No Match Found"
    End If

 

[cbrf23]

Try this:

Sub test()
    Dim intX                As Integer
    Dim strSearch           As String
    Dim rngFound            As Range
    Dim rngFoundCol         As Range
    
    strSearch = "GL Code"
    On Error Resume Next
    Set rngFound = Cells.Find(What:=strSearch, After:=Range("A1"), LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)
    On Error GoTo 0
    If rngFound Is Nothing Or rngFound.Column = 1 Then Exit Sub
    
    Application.ScreenUpdating = False
    Set rngFoundCol = rngFound.EntireColumn
    Range("A:A").Insert Shift:=xlToLeft
    Range("A:A").Value = rngFoundCol.Value
    rngFoundCol.Delete
    Application.ScreenUpdating = True
End Sub

 

[cbrf23]

I didnt see alphafrog's response there, but if you want to copy formatting, etc, use his method of copy/destination rather than what I did with value=value - that only copies the values. Not sure what your needs are.

 

[dd*]

i think it was something like

Columns("A:A").Select


the result of the above code, is instead of selecting column A, it selects the whole spreadsheet.
i believe this is due to merged cells. this is how the users have created and worked with the spreadsheets, and copying & pasting data between spreadsheets.


many thanks again

dd