CODEWITHSUNDEEP
excelvba
AME
AME

Reputation: 5300

Selecting Ranges in Excel VBA

I would like to create a VBA subroutine that searches a table for the first column header named "Phonetic Name". Then finds the absolute last cell in the table, on the bottom right corner, and store a variable as the cell coordinate one row above the last cell. The subroutine would then select all cells between the first cell "Phonetic Name" and the "LastCell" variable.

Dim LastCol As Integer

TL = ActiveSheet.Range("A:A").Find("Phonetic Name", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True).Row

Set LastRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious)

With ActiveSheet
    LastCol = .Cells(TL, .Columns.Count).End(xlToLeft).Column
End With

Set LastCell = ActiveSheet.Cells(LastRow.Row - 1, LastCol)
'I would like to do something like the following... 
ActiveSheet.Range("TL:LastCell").Select
Selection.Copy

How can I re-write this logic in a way that is VBA friendly?

Upvotes: 0

Views: 2484

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Dim LastCol As Integer  
Dim TL as Range

Set TL = ActiveSheet.Range("A:A").Find("Phonetic Name", _
                 LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)  

If Not TL Is Nothing Then

    Set LastRow = Cells.Find("*", [a1], , , xlByRows, xlPrevious)  
    With ActiveSheet     
        LastCol = .Cells(TL.Row, .Columns.Count).End(xlToLeft).Column 
    End With  
    Set LastCell = ActiveSheet.Cells(LastRow.Row - 1, LastCol) 
    ActiveSheet.Range(TL,LastCell).Copy

End If

Upvotes: 2

Related Questions