Excel Forum

Code: Select all

            Dim lsRow As Long
    With Sheets("Sheet1")
        lsRow = .Range("a" & .Rows.Count).End(xlUp).Row
    End With
     ListBox1.RowSource = Sheets("Sheet1").Range("A2:i" & lsRow).Address(external:=True)
    With ListBox1
        .ListIndex = .ListCount - 1
        .Selected(.ListCount - 1) = True
    End With
             End With

Code: Select all

Private Sub CommandButton1_Click()
'Other code
    Dim ds As Double, ds1 As Variant
    Dim dt As Double, dt1 As Variant
    ds1 = Split(Me.tbStDate.Text, "/")
    dt1 = Split(Me.tbEndDate.Text, "/")
    ds = DateSerial(ds1(2), ds1(1), ds1(0))
    dt = DateSerial(dt1(2), dt1(1), dt1(0))
    With ThisWorkbook.Worksheets("Sheet1")
        Me.lblCountif = Application.WorksheetFunction.SumIfs(.Range("number"), _
            .Range("WhatRng"), Me.TextBox1, _
            .Range("DateRng"), ">=" & ds, _
            .Range("DateRng"), "<=" & dt)
            Me.ListBox1.Clear
            Dim lsRow As Long
 '   With Sheets("Sheet1")
  '      lsRow = .Range("a" & .Rows.Count).End(xlUp).Row
'    End With
    i = Application.WorksheetFunction.Match(ds, Range("DateRng"), 0)
    j = Application.WorksheetFunction.Match(dt, Range("DateRng"), 0)
     ListBox1.RowSource = Sheets("Sheet1").Range("a" & i, "i" & j).Address(external:=True)
    With ListBox1
        .ListIndex = .ListCount - 1
        .Selected(.ListCount - 1) = True
    End With
             End With
End Sub