none
Excel VBA select date range

    Question

  • Hiya,

    I need to select a range of cells based on the date. I have a table in excel with 30days of data. I need to select the first 15 days, then create a graph. Once created I need to select the next 15 days.

    THe question is how do you select a date range in VBA?

    Cheers

    Craig

    Sunday, May 30, 2010 9:52 PM

Answers

  • On 31/05/2010 8:13 AM, Craig_Wilson wrote:
    >
    > Hi Doug,
    >
    > Thanks for the information, but I have more then 30 rows, there is
    > more then 1 row per date.
    >
    > Here is an example.
    >
    > Date Opened: Date of Work: Type
    >
    > 1/02/2008 11:39:01 AM 20/08/2009 9:24:01 AM Problem
    > 1/05/2009 4:36:35 PM 4/05/2009 4:38:40 PM Problem
    > 1/07/2009 10:31:12 AM 19/08/2009 3:04:29 PM Error
    > 1/07/2009 9:20:00 AM 2/07/2009 4:50:47 PM Request
    > 1/08/2008 11:21:06 AM 8/01/2009 12:15:03 PM Error
    > 1/09/2008 9:14:39 AM 19/08/2009 2:01:29 PM Error
    > 1/09/2009 1:13:00 PM 3/09/2009 12:13:50 PM Request
    > 1/09/2009 10:29:41 AM 9/09/2009 12:40:24 PM Request
    > 1/09/2009 10:40:07 AM 8/11/2009 2:42:27 PM Request
    >
    > You can see the first column has the date. I need to select x number
    > of days.
    >
    > Cheers
    >
    > Craig
    >
    Hi Craig,
     
    Using the following, xlRange1 will contain the records with dates in the
    first 15 days and xlRange2 will contain the records with the dates in
    the balance of the data:
     
    Dim i As Long, j As Long
    Dim xlrange As Range, xlRange1 As Range, xlRange2 As Range
    Dim startDate As Date
    With Worksheets(1)
    startDate = .Range("A1").Value
    Set xlrange = .Range("A1", Range("A1").End(xlDown))
    j = xlrange.Rows.Count
    For i = 2 To j
    If .Range("A" & i).Value < startDate + 14 Then
    i = i + 1
    Else
    Exit For
    End If
    Next i
    Set xlRange1 = .Range("A1", "A" & i)
    Set xlRange1 = .Range(xlRange1, xlRange1.End(xlToRight))
    Set xlRange2 = .Range("A" & i + 1, "A" & j)
    Set xlRange2 = .Range(xlRange2, xlRange2.End(xlToRight))
    End With
     
     
    --
    Hope this helps,
    Doug Robbins - Word MVP
    dkr[atsymbol]mvps[dot]org
    Please reply to the forum unless you wish to obtain my services on a professional basis.
     
     

    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Tim Li Tuesday, June 08, 2010 9:17 AM
    Sunday, May 30, 2010 11:52 PM
  • This will create an array of range addresses that you can use to build the graphs. It does assume the dates are in order, and you will need to add the header row when you build the graphs.

    Dim aryRanges As VariantDim LastRow As Long
    Dim FindRow As Long
    Dim aryCount As Long
    Dim StartCell As Range
    Dim rng As Range

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ReDim aryRanges(1 To 1)
    .Columns(1).Insert
    With .Range("A2").Resize(LastRow - 1)

    .Formula = "=INT(B2)"
    .Value2 = .Value2
    End With

    Set StartCell = .Range("B2")
    Do

    FindRow = Application.Match(StartCell.Value2 + 15, .Columns(1))
    aryCount = aryCount + 1
    ReDim Preserve aryRanges(1 To aryCount)
    aryRanges(aryCount) = StartCell.Offset(0, 1).Resize(FindRow - StartCell.Row + 1, 3).Address
    Set StartCell = .Cells(FindRow + 1, "B")
    Loop Until FindRow >= LastRow

    .Columns(1).Delete
    End With

    --

    HTH

    Bob

    <Craig_Wilson> wrote in message news:59ed463f-726c-49d2-b8e6-2732e534ab79@communitybridge.codeplex.com...
    Hi Doug,



    Thanks for the information, but I have more then 30 rows, there is more then 1 row per date.

    Here is an example.

    Date Opened: Date of Work: Type

    1/02/2008 11:39:01 AM 20/08/2009 9:24:01 AM Problem
    1/05/2009 4:36:35 PM 4/05/2009 4:38:40 PM Problem
    1/07/2009 10:31:12 AM 19/08/2009 3:04:29 PM Error
    1/07/2009 9:20:00 AM 2/07/2009 4:50:47 PM Request
    1/08/2008 11:21:06 AM 8/01/2009 12:15:03 PM Error
    1/09/2008 9:14:39 AM 19/08/2009 2:01:29 PM Error
    1/09/2009 1:13:00 PM 3/09/2009 12:13:50 PM Request
    1/09/2009 10:29:41 AM 9/09/2009 12:40:24 PM Request
    1/09/2009 10:40:07 AM 8/11/2009 2:42:27 PM Request

    You can see the first column has the date. I need to select x number of days.

    Cheers

    Craig



    • Marked as answer by Tim Li Tuesday, June 08, 2010 9:17 AM
    Monday, May 31, 2010 2:46 PM

All replies

  • On 31/05/2010 7:52 AM, Craig_Wilson wrote:
    >
    > Hiya,
    >
    > I need to select a range of cells based on the date. I have a table in
    > excel with 30days of data. I need to select the first 15 days, then
    > create a graph. Once created I need to select the next 15 days.
    >
    > THe question is how do you select a date range in VBA?
    >
    > Cheers
    >
    > Craig
    >
     
    For the first 15 days, assuming that the data starts in cell A1, use
     
    Dim xlRange As Range
    Set xlRange = Worksheets(1).Range("A1", "A15")
    Set xlRange = Worksheets(1).Range(xlRange, xlRange.End(xlToRight))
    xlRange.Select
     
    For the second 15 days, replace the A1 with A16 and the A15 with A30
     
    --
    Hope this helps,
    Doug Robbins - Word MVP
    dkr[atsymbol]mvps[dot]org
    Please reply to the forum unless you wish to obtain my services on a professional basis.
     
     

    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Sunday, May 30, 2010 10:04 PM
  • Hi Doug,

     

    Thanks for the information, but I have more then 30 rows, there is more then 1 row per date.

    Here is an example.

    Date Opened: Date of Work: Type

    1/02/2008 11:39:01 AM 20/08/2009 9:24:01 AM Problem
    1/05/2009 4:36:35 PM 4/05/2009 4:38:40 PM Problem
    1/07/2009 10:31:12 AM 19/08/2009 3:04:29 PM Error
    1/07/2009 9:20:00 AM 2/07/2009 4:50:47 PM Request
    1/08/2008 11:21:06 AM 8/01/2009 12:15:03 PM Error
    1/09/2008 9:14:39 AM 19/08/2009 2:01:29 PM Error
    1/09/2009 1:13:00 PM 3/09/2009 12:13:50 PM Request
    1/09/2009 10:29:41 AM 9/09/2009 12:40:24 PM Request
    1/09/2009 10:40:07 AM 8/11/2009 2:42:27 PM Request

    You can see the first column has the date. I need to select x number of days.

    Cheers

    Craig

     

    Sunday, May 30, 2010 10:13 PM
  • On 31/05/2010 8:13 AM, Craig_Wilson wrote:
    >
    > Hi Doug,
    >
    > Thanks for the information, but I have more then 30 rows, there is
    > more then 1 row per date.
    >
    > Here is an example.
    >
    > Date Opened: Date of Work: Type
    >
    > 1/02/2008 11:39:01 AM 20/08/2009 9:24:01 AM Problem
    > 1/05/2009 4:36:35 PM 4/05/2009 4:38:40 PM Problem
    > 1/07/2009 10:31:12 AM 19/08/2009 3:04:29 PM Error
    > 1/07/2009 9:20:00 AM 2/07/2009 4:50:47 PM Request
    > 1/08/2008 11:21:06 AM 8/01/2009 12:15:03 PM Error
    > 1/09/2008 9:14:39 AM 19/08/2009 2:01:29 PM Error
    > 1/09/2009 1:13:00 PM 3/09/2009 12:13:50 PM Request
    > 1/09/2009 10:29:41 AM 9/09/2009 12:40:24 PM Request
    > 1/09/2009 10:40:07 AM 8/11/2009 2:42:27 PM Request
    >
    > You can see the first column has the date. I need to select x number
    > of days.
    >
    > Cheers
    >
    > Craig
    >
    Hi Craig,
     
    Using the following, xlRange1 will contain the records with dates in the
    first 15 days and xlRange2 will contain the records with the dates in
    the balance of the data:
     
    Dim i As Long, j As Long
    Dim xlrange As Range, xlRange1 As Range, xlRange2 As Range
    Dim startDate As Date
    With Worksheets(1)
    startDate = .Range("A1").Value
    Set xlrange = .Range("A1", Range("A1").End(xlDown))
    j = xlrange.Rows.Count
    For i = 2 To j
    If .Range("A" & i).Value < startDate + 14 Then
    i = i + 1
    Else
    Exit For
    End If
    Next i
    Set xlRange1 = .Range("A1", "A" & i)
    Set xlRange1 = .Range(xlRange1, xlRange1.End(xlToRight))
    Set xlRange2 = .Range("A" & i + 1, "A" & j)
    Set xlRange2 = .Range(xlRange2, xlRange2.End(xlToRight))
    End With
     
     
    --
    Hope this helps,
    Doug Robbins - Word MVP
    dkr[atsymbol]mvps[dot]org
    Please reply to the forum unless you wish to obtain my services on a professional basis.
     
     

    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by Tim Li Tuesday, June 08, 2010 9:17 AM
    Sunday, May 30, 2010 11:52 PM
  • This will create an array of range addresses that you can use to build the graphs. It does assume the dates are in order, and you will need to add the header row when you build the graphs.

    Dim aryRanges As VariantDim LastRow As Long
    Dim FindRow As Long
    Dim aryCount As Long
    Dim StartCell As Range
    Dim rng As Range

    With ActiveSheet

    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ReDim aryRanges(1 To 1)
    .Columns(1).Insert
    With .Range("A2").Resize(LastRow - 1)

    .Formula = "=INT(B2)"
    .Value2 = .Value2
    End With

    Set StartCell = .Range("B2")
    Do

    FindRow = Application.Match(StartCell.Value2 + 15, .Columns(1))
    aryCount = aryCount + 1
    ReDim Preserve aryRanges(1 To aryCount)
    aryRanges(aryCount) = StartCell.Offset(0, 1).Resize(FindRow - StartCell.Row + 1, 3).Address
    Set StartCell = .Cells(FindRow + 1, "B")
    Loop Until FindRow >= LastRow

    .Columns(1).Delete
    End With

    --

    HTH

    Bob

    <Craig_Wilson> wrote in message news:59ed463f-726c-49d2-b8e6-2732e534ab79@communitybridge.codeplex.com...
    Hi Doug,



    Thanks for the information, but I have more then 30 rows, there is more then 1 row per date.

    Here is an example.

    Date Opened: Date of Work: Type

    1/02/2008 11:39:01 AM 20/08/2009 9:24:01 AM Problem
    1/05/2009 4:36:35 PM 4/05/2009 4:38:40 PM Problem
    1/07/2009 10:31:12 AM 19/08/2009 3:04:29 PM Error
    1/07/2009 9:20:00 AM 2/07/2009 4:50:47 PM Request
    1/08/2008 11:21:06 AM 8/01/2009 12:15:03 PM Error
    1/09/2008 9:14:39 AM 19/08/2009 2:01:29 PM Error
    1/09/2009 1:13:00 PM 3/09/2009 12:13:50 PM Request
    1/09/2009 10:29:41 AM 9/09/2009 12:40:24 PM Request
    1/09/2009 10:40:07 AM 8/11/2009 2:42:27 PM Request

    You can see the first column has the date. I need to select x number of days.

    Cheers

    Craig



    • Marked as answer by Tim Li Tuesday, June 08, 2010 9:17 AM
    Monday, May 31, 2010 2:46 PM