Selecting Ranges in Excel VBA

Question

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?

I think ActiveSheet.Range(TL,LastCell).Copy is what you're looking for (but remove the .Row from the end of the .Find() line) — Tim Williams
– Tim Williams, Commented Aug 24, 2012 at 0:48

Tim Williams · Accepted Answer · 2012-08-24 14:51:40Z

2

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

edited Aug 24, 2012 at 14:51

answered Aug 24, 2012 at 0:52

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Doug Glancy Over a year ago

Change TL to TL.Row, i.e., LastCol = .Cells(TL, Columns.Count).End(xlToLeft).Column to LastCol = .Cells(TL.Row, Columns.Count).End(xlToLeft).Column Also, consider expanding your With statement to include all ActiveSheet references, and declaring all your variables.

Collectives™ on Stack Overflow

Selecting Ranges in Excel VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related