VBA autofilter

I

i_malc

New Member
Joined
Aug 28, 2010
Messages
14
Can anyone help me please? I’m new to VBA and I’ve written code to run an AutoFilter. It’s only a few lines and I should be able to see what the error is but just cant. It runs and it shows the headings and deletes the rest of the range.

Sub StartEnd()

Dim StDate As Long: StDate = (AO1)
Dim EndDate As Long: EndDate = (AP1)

Set sh = ThisWorkbook.Sheets("sheet1")
sh.AutoFilterMode = False
Range("A1:AN4900").AutoFilter 11, ">=" & StDate ', xlAnd, "<=" & EndDate
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Try this:
Dim StDate As Long: StDate = Range("AO1")
Dim EndDate As Long: EndDate = Range("AP1")
 
Last edited:
Upvote 0
Thanks, But its still doesn’t filter. The column with the date in is named “Batched” and the date is in format “1 Feb 06” does the column need formatting?
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1571"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]KeyCode[/TD]
[TD="align: center"]Ttype[/TD]
[TD="align: center"]Acct[/TD]
[TD="align: center"]Ref[/TD]
[TD="align: center"]Tdate[/TD]
[TD="align: center"]Goods[/TD]
[TD="align: center"]Vat[/TD]
[TD="align: center"]PxRef[/TD]
[TD="align: center"]PxDate[/TD]
[TD="align: center"]Bal[/TD]
[TD="align: center"]Batch[/TD]
[TD="align: center"]Period[/TD]
[TD="align: center"]Current[/TD]
[TD="align: center"]YourRef[/TD]
[TD="align: center"]Grates[/TD]
[TD="align: center"]Vrates[/TD]
[TD="align: center"]Pmethods[/TD]
[TD="align: center"]NCodes[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]DOD01[/TD]
[TD="align: center"]247361[/TD]
[TD="align: center"]19991110[/TD]
[TD="align: center"]16.7[/TD]
[TD="align: center"]2.92[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]19.62[/TD]
[TD="align: center"]22 Nov 99[/TD]
[TD="align: center"]200001[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]16.7[/TD]
[TD="align: center"]2.92[/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]DOD01[/TD]
[TD="align: center"]247479[/TD]
[TD="align: center"]19991110[/TD]
[TD="align: center"]5.5[/TD]
[TD="align: center"]0.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]6.46[/TD]
[TD="align: center"]22 Nov 99[/TD]
[TD="align: center"]200001[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5.5[/TD]
[TD="align: center"]0.96[/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]DOD01[/TD]
[TD="align: center"]247744[/TD]
[TD="align: center"]19991112[/TD]
[TD="align: center"]9.2[/TD]
[TD="align: center"]1.61[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]10.81[/TD]
[TD="align: center"]22 Nov 99[/TD]
[TD="align: center"]200001[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9.2[/TD]
[TD="align: center"]1.61[/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]DOD01[/TD]
[TD="align: center"]247925[/TD]
[TD="align: center"]19991115[/TD]
[TD="align: center"]16.5[/TD]
[TD="align: center"]2.89[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]19.39[/TD]
[TD="align: center"]22 Nov 99[/TD]
[TD="align: center"]200001[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]16.5[/TD]
[TD="align: center"]2.89[/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: center"]INV[/TD]
[TD="align: center"]DOD01[/TD]
[TD="align: center"]248121[/TD]
[TD="align: center"]19991116[/TD]
[TD="align: center"]123.96[/TD]
[TD="align: center"]21.69[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]145.65[/TD]
[TD="align: center"]22 Nov 99[/TD]
[TD="align: center"]200001[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]123.96[/TD]
[TD="align: center"]21.69[/TD]
[TD="align: center"][/TD]
[TD="align: center"]250[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
In order for it to work, the column with the dates and the cell holding the Start and End dates must ALL be entered as valid dates. If eny are entered as Text, it will not work properly.
What are your date values in cells "AO1" and "AP1"?
 
Last edited:
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 67"]
<tbody>[TR]
[TD="class: xl65, width: 67, align: right"][/TD]
[/TR]
</tbody>[/TABLE]
AO1[/TD]
[TD]AP1[/TD]
[/TR]
[TR]
[TD]1-Feb-06[/TD]
[TD]01-Dec-19[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
But are those entries and the entries in your "BATCH" column ALL entered as dates, or are some entered as text?

Note that based on the sample data you posted, NONE of those entries meet your filter criteria.
 
Upvote 0
Thanks for your help so far but I've tried different formats in cells AO1 & AP1. I think it might be the Batch column. Is there any way to convert the column to date format? If possible to email you my file.
 
Upvote 0
Changing the cell format will not make a difference - it is the data type that must be changed.

Try the following and tell me what happens:
- Temporarily change the cell formats cells AO1, AP1, and your BATCH column to "GENERAL". What happens? Do they all change to big numbers, like 40000?
If they do NOT, then that is the cells that we must fix, as the entries are text entries and not date entries.
 
Upvote 0
Both AO1 & AP1 gov to high numbers but Batch dosen't. Can anything be done to change Batch to date format, If not I've notice that column 5 Tdate has a date in reverse order can anything be done with this column?
 
Upvote 0
You should be able to use Text to Columns to fix both of those.

For the Batch column do the following:
1. Select the entire column
2. Choose "Text to Columns" from the "Data"
3. On Step 1, choose the "Delimited" option and click Next
4. Hit Next again to get to Step 3
5. On Step 3, choose the "Date" radio button, and then "DMY date format option from the drop-down box
6. Click Finish

That should fix that column.

For your other column, follow the same steps, except choosing the "YMD" date format option.
Note those indicate the order of the data ("YMD" means Year is first, then Month, then Date). Then it knows how to read and convert it.

Once those values are converted to valid dates using that technique, your Filters should work, and you can also choose any date format that you want for those columns.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
autofilter
Replies
2
Views
269
vmjan02
V
V
  • Question Question
Excel VBA - How to modify my code to include a command that prints data to PDF
Replies
1
Views
505
VanGirl
V
airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
965
Alex Blakenburg
A
P
  • Solved
run time error 380 rowsource property invalid property value
2 3
Replies
24
Views
789
Patriot2879
P
I
  • Question Question
Filter Status Using Checkbox
Replies
1
Views
193
iggyboy29
I

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top