VBA code to filter by exact date via inputbox

  • Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.


    Remember to desensitize the data.

  • Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.


    Post a link to any other forums where you have asked the same question.


    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.


    Go to https://www.excelguru.ca/content.php?184 to understand why we ask you to do this.


    I have added the crosspost reference for you today. Please comply with this and all our rules in the future


    https://www.mrexcel.com/forum/…ng-text-box-vba-code.html

  • Sorry about that double posting, I see your point now.
    Yes, user Momentman (thank you!) came up with that code below, which works after I formated properly the dates in column.
    Now all I guess all I need to do is need is modify that line Criteria1:=ActiveCell.Value to value from inputbox, right?


    Code
    1. Sub FilterColumnJ()
    2. Dim Lastrow As Long
    3. Lastrow = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row
    4. ActiveSheet.Range("A1:J" & Lastrow).AutoFilter Field:=10,Criteria1:=ActiveCell.Value
    5. End Sub
  • fotoDj maybe like this

    Code
    1. Sub FilterColumnJ()
    2. Dim Lastrow As Long
    3. Dim ap as string
    4. Dim dt as date,t1 as date
    5. ap = application.inputBox("get date")
    6. dt = cdate(ap)
    7. t1 =dateserial(dt,dt,dt)
    8. Lastrow = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row
    9. ActiveSheet.Range("A1:J" & Lastrow).AutoFilter Field:=10,Criteria1:=t1
    10. End Sub
  • [USER="275839"]graha_karya[/USER] looks like code should work, but I'm getting msg "Run-time error "6" overflow" , maybe it is date format error? My date format in excel is formatted mm/dd/yyyy

  • Is Your data in col j is date or try this give me feedback

  • fotodj this work for for me

    Code
    1. Sub ggg()
    2. Dim ap, dt As Date, dt1 As Date, lr&: lr = ActiveSheet.UsedRange.SpecialCells(11).Row
    3. With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)
    4. ap = Application.InputBox("get date")
    5. dt = CDate(ap)
    6. .AutoFilter 10, "=" & dt
    7. End With
    8. End Sub
  • [USER="275839"]graha_karya[/USER] I am attaching my file...probably I keep making some kind of simple mistake, BTW it is Excel 2016. Appreciate your help and patience!


    [ATTACH]n1210822[/ATTACH]

  • Final working code:


    Code
    1. Sub FilterDate()
    2. Dim ap As Date, dt As Date, dt1 As Date Dim lr As Long lr = ActiveSheet.UsedRange.SpecialCells(11).Row
    3. With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)
    4. ap = Application.InputBox("get date")
    5. dt = CDate(ap)
    6. .AutoFilter 10, Criteria1:=">=" & dt, _
    7. Operator:=xlAnd, Criteria2:="<" & dt + 1
    8. End With End Sub
  • [USER="334111"]toucherts[/USER] Aren't you six month late with that comment, I apologized for that mistake already, you would see it if you've bothered to read more carefully, BTW you just joined up today and you school people already?
    The last thing you want to do is waste people's time reading your useless remark.