Selecting range up to blank row

**FoxIII** · 02-08-2009, 10:52 AM

I am having a few problems selecting a range. What I am trying to do is select a range where the number of columns stay the same, but the rows are variable; ranging from one row upwards. At the end of the rows, there is a blank row. I currently have;

Sub CopyRange()
  Range("A7", "L7").Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
End Sub

However, the selection of the range down goes past the blank row and I cannot find out how to select just down to the blank row.

I am not worried as to whether the blank row is included in the selection or not, just that it selects the rows that are required.

TIA

**Bob Phillips** · 02-08-2009, 12:14 PM

Dim LastRow As Long

    LastRow = Cells.Find(What:="*", _
                        After:=Range("A7"), _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext).Row
    Range("A7:L7").Resize(LastRow - 6).Select
    Selection.Copy