Excel VBA to find cell and select range

[ash060200]

Hi,
I am trying to create a macro that will select a cell using the find feature and then using that cell as the first cell for a range. I can use the find feature to select a cell and use that cell to either select the last cell in that column or the last cell in that row but not both. I need to select the last cell in both the row and the column. I am not having much luck and would appreciate any help.

Dim lngLastRow As Long
Dim lngLastCol As Long


With Worksheets("Sheet1").Cells
Set mc = .Find("member id", After:=.Range("A2"), LookIn:=xlValues)
If Not mc Is Nothing Then
mc.Select
End If
End With


mc.Select

Range(ActiveCell, ActiveCell.End(xlToRight), ActiveCell.End(x1Down)).Select
Selection.Copy



** I have also tried this line but it does not like the variable starting cell.
Range("mc: " & strLastCol & lngLastRow).SpecialCells(xlCellTypeVisible).Select
Selection.Copy

 

[Osvaldo Palmeiro]

Hi. Try:

Sub test()
 Dim lngLastRow As Long
 Dim lngLastCol As Long
 Dim mc As Range
  With Worksheets("Sheet1").Cells
   Set mc = .Find("member id", After:=.Range("A2"), LookIn:=xlValues)
    If Not mc Is Nothing Then
     lngLastRow = .Cells(Rows.Count, mc.Column).End(xlUp).Row
     lngLastCol = .Cells(mc.Row, Columns.Count).End(xlToLeft).Column
     .Range(mc, Cells(lngLastRow, lngLastCol)).Copy
   End If
  End With
End Sub

 

[ash060200]

Thank you so much for the information. I did find another post that showed how to put it all in one line using Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select.

I am glad to see your response to know what I missed. This forum is a great resource and a lot of information. Thank you again.