• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

SELECTED RANGE

I am trying to get the selected region between dates.File attached
Book1.xlsm
0
Svgmassive
Asked:
Svgmassive
  • 2
1 Solution
 
Martin LissRetired ProgrammerCommented:
Sub GetRange()

Dim lngLastRow As Long
Dim lngRowEnd As Long
Dim lngRowStart As Long

lngLastRow = Range("B1048576").End(xlUp).Row

' Find start date
For lngRowStart = ActiveCell.Row To 1 Step -1
    If IsDate(Cells(lngRowStart, 2)) Then
        Exit For
    End If
Next

' Find end date
For lngRowEnd = ActiveCell.Row + 1 To lngLastRow
    If IsDate(Cells(lngRowEnd, 2)) Then
        lngRowEnd = lngRowEnd - 1
        Exit For
    End If
Next

Range("B" & lngRowStart & ":AF" & lngRowEnd).Select
End sub

Open in new window

0
 
Glenn RayExcel VBA DeveloperCommented:
I've tweaked Martin's code to pick up all rows if one chooses a cell at or after the last date shown (selects to the last used cell's row)

Sub GetRange()
    Dim lngLastRow As Long
    Dim lngRowEnd As Long
    Dim lngRowStart As Long
    Dim lngLastUsedRow As Long
    
    lngLastRow = Range("B" & Cells.Rows.Count).End(xlUp).Row
    lngLastUsedRow = Cells.SpecialCells(xlLastCell).Row
    
    ' Find start date
    For lngRowStart = ActiveCell.Row To 1 Step -1
        If IsDate(Cells(lngRowStart, 2)) Then
            Exit For
        End If
    Next lngRowStart
    
    ' Find end date
    If ActiveCell.Row + 1 > lngLastRow Then
        lngRowEnd = lngLastUsedRow
    Else
        For lngRowEnd = ActiveCell.Row + 1 To lngLastRow
            If IsDate(Cells(lngRowEnd, 2)) Then
                lngRowEnd = lngRowEnd - 1
                Exit For
            End If
        Next lngRowEnd
    End If
    Range("B" & lngRowStart & ":AF" & lngRowEnd).Select
End Sub

Open in new window



-Glenn
0
 
Martin LissRetired ProgrammerCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2014
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now