I need to filter Sheet1 by exact date using text inputbox - vba code. Dates are in column J... What is the easiest way to do it?
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
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?
fotoDj maybe like thisCode
[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 feedbackCode
- Sub FilterColumnJ()
- Dim Lastrow As Long
- Dim ap as string
- Dim dt as date,t1 as date
- dim t2 as long
- ap = application.inputBox("get date")
- dt = cdate(ap)
- t1 =format(dateserial(dt,dt,dt),"mm/dd/yyyy")
- t2 = dt 'if still error try change Criteria1 with t2
- Lastrow = ActiveSheet.Range("J" & Rows.Count).End(xlUp).Row
- ActiveSheet.Range("A1:J" & Lastrow).AutoFilter Field:=10,Criteria1:=t1
- End Sub
[USER="275839"]graha_karya[/USER] , still getting the same error on both t1 and t2, I am testing it on data input 10/24/2018 in the inputtbox
[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!
try this file
[USER="275839"]graha_karya[/USER] After any date input filter shows only first row...
Final working code:Code
- Sub FilterDate()
- Dim ap As Date, dt As Date, dt1 As Date Dim lr As Long lr = ActiveSheet.UsedRange.SpecialCells(11).Row
- With ActiveSheet.[a1].CurrentRegion.Resize(lr, 10)
- ap = Application.InputBox("get date")
- dt = CDate(ap)
- .AutoFilter 10, Criteria1:=">=" & dt, _
- Operator:=xlAnd, Criteria2:="<" & dt + 1
- 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.