How can I make xlDown for selected 4 cells ("F9:I9") - from those 4 cells I want to keep selection to last rows data ("F50:I50").

Many Thx

I expect to select all data from F9 to I50 - but always when I used xlDown I do it only for one column.

CodePudding user response：

I just found out using the macro recorder: I selected cells "F9" to "F11" and pressed Ctrl KeyDown, it gave me following macro:

Range(Selection, Selection.End(xlDown)).Select

I just changed the original selection by Range("F9:I9"), and this is what I got:

Range(Range("F9:I9"), Range("F9:I9").End(xlDown)).Select

(There's even no need to select that entire range, it's just an example.)

CodePudding user response：

Reference Non-Empty Range When Given the First Row

• There are many ways how to do it. The one I consider most reliable and most often used is using the Find method to find the Last Cell i.e. the bottom-most non-empty cell in the columns.
• Both solutions do the same. They just use different arguments.

Solution 1

Utilization

Sub RefRangeTest()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    Dim frrg As Range: Set frrg = ws.Range("F9:I9")
    
    Dim rg As Range: Set rg = RefRange(frrg)
    
    If Not rg Is Nothing Then
        Debug.Print rg.Address(0, 0)
    Else
        Debug.Print "The area is empty."
    End If
    
End Sub

The Function

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Purpose:      References the range from a given single-row range
'               to the bottom-most non-empty cell of the given row's columns.
' Remarks:      It will fail if the worksheet is filtered.
'               It will not fail if rows or columns are hidden.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function RefRange( _
    ByVal FirstRowRange As Range) _
As Range
    With FirstRowRange.Rows(1)
        Dim cel As Range: Set cel = .Resize(.Worksheet.Rows.Count - .Row   1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If Not cel Is Nothing Then Set RefRange = .Resize(cel.Row - .Row   1)
    End With
End Function

Solution 2

Utilization

Sub RefRangeRefTest()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range: Set rg = RefRangeRef(ws, "F9:I9")
    
    If Not rg Is Nothing Then
        Debug.Print rg.Address(0, 0)
    Else
        Debug.Print "The area is empty."
    End If

End Sub

The Function

Function RefRangeRef( _
    ByVal ws As Worksheet, _
    Optional ByVal FirstRowAddress As String = "A2") _
As Range
    With ws.Range(FirstRowAddress).Rows(1)
        Dim cel As Range: Set cel = .Resize(ws.Rows.Count - .Row   1) _
            .Find("*", , xlFormulas, , xlByRows, xlPrevious)
        If Not cel Is Nothing Then Set RefRangeRef = .Resize(cel.Row - .Row   1)
    End With
End Function