Select area below used range

Question

Can someone please help me out with a line or two of VBA that will select the entire spreadsheet (all columns/all rows) beneath the current used range (regardless of the number of columns used)?

Thanks for your assistance.

Answer 1

Dim myRange As Range

Set myRange = Selection.Cells(1, 1)

With ActiveWorkbook.Worksheets(1)

    Set myRange = .Range(myRange, myRange.End(2))

    Set myRange = .Range(myRange, myRange.End(4))

End With

myRange.Select

-- Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my

services on a paid consulting basis.

Doug Robbins - Word MVP, Posted via the NNTP Bridge

"XP3" wrote in message news:*** Email address is removed for privacy ***...

Can someone please help me out with a line or two of VBA that will select the entire spreadsheet (all columns/all rows) beneath the current used range (regardless of the number of columns used)?

Thanks for your assistance.

---

Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org

Answer 2

The usedrange doesn't have to start in Row 1 (or column A).

If you want to select the rows under the usedrange, you could use:

Option Explicit

Sub testme()

    Dim NextRow As Long

    Dim wks As Worksheet

    Set wks = ActiveSheet

    With wks

        With .UsedRange

            NextRow = .Rows(.Rows.Count).Row + 1

        End With

        If NextRow > .Rows.Count Then

            MsgBox "No rows under that used range!"

        Else

            .Rows(NextRow & ":" & .Rows.Count).Select

        End If

    End With

End Sub

XP3 wrote:

Can someone please help me out with a line or two of VBA that will select the entire spreadsheet (all columns/all rows) beneath the current used range (regardless of the number of columns used)?

Thanks for your assistance.

--

Dave Peterson

Answer 3

I think this is what you want?

Sub SelectBelowUsedRange()

   Range(Cells(ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row + 1, "A"), [XFD1048576]).Select

End Sub

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

Answer 4

Hi Bob,

Thanks for this, however, it only selects one row...I need it to select ALL rows beneath the used range...

Answer 5

Sub SelectBelowUsedRange()

   LR=ActiveSheet.Usedrange.Row+ActiveSheet.UsedRange.Rows.Count-1   'Usedrange may be only F3:G4 in which case this is 3+2-1 or 4

   Rows(LR+1).Select

End Sub

Bob Umlas

Excel MVP