CODEWITHSUNDEEP
vbaexcel
Daan
Daan

Reputation: 1437

Excel: Selecting all rows until empty cell

I'd to select all rows from 2 adjacent columns, starting with the row below the header (this is fixed) and ending with the row before the first row with a blank cell.

Given the following the example... 

    A       B
1   Item    Amount
2   Item1   1
3   Item2   4
4           2
5

I'd like to select the range A2:B3. The first row is always row 2, the last row is row 3 because it is the row before the first row with a blank cell.

My goal is to use this selection to automatically sort and add borders to the rows after I've completely filled in the row below the current last row. In this example, I'd have to fill in A4 to make the row part of the selection and apply the aforementioned actions.

I hope anyone can help me.. Thanks in advance!

EDIT

I've come up with a solution, based on chris neilsen's solution:

Dim rng As Range
With Worksheets("YourSheet")
    Set rng1 = .Range(.Cells(2, 1), .Cells(2, 2).End(xlDown))
    Set rng2 = .Range(.Cells(2, 2), .Cells(2, 1).End(xlDown))
    Set r = Application.Intersect(rng1, rng2)

End With

Upvotes: 4

Views: 45403

Answers (2)

user2063626
user2063626

Reputation:

Try below code :

Sub sample()
    Dim lastRow As Long
    lastRow = Range("A65000").End(xlUp).Row

    Dim rng As Range, newRng As Range

    Set rng = Range("A2:A" & lastRow)
    Set newRng = rng.Resize(, 2)

    If Not newRng Is Nothing Then
        newRng.Sort key1:=Range("A2")
    End If

    newRng.BorderAround xlContinuous, xlMedium, xlColorIndexAutomatic

End Sub

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53126

try this

Dim rng as Range
With Worksheets("YourSheet")
    Set rng = .Range(.Cells(1,2), .Cells(1,2).End(xlDown)).Resize(,2)
End With

the variable rng will now be set to A2:B3

Upvotes: 4

Related Questions