VBA autofilter

[i_malc]

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

 

[Joe4]

Try this:
Dim StDate As Long: StDate = Range("AO1")
Dim EndDate As Long: EndDate = Range("AP1")

 

[i_malc]

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?

KeyCode Ttype Acct Ref Tdate Goods Vat PxRef PxDate Bal Batch Period Current YourRef Grates Vrates Pmethods NCodes 16 INV DOD01 247361 19991110 16.7 2.92 19.62 22 Nov 99 200001 1 0 16.7 2.92 250 17 INV DOD01 247479 19991110 5.5 0.96 6.46 22 Nov 99 200001 1 0 5.5 0.96 250 18 INV DOD01 247744 19991112 9.2 1.61 10.81 22 Nov 99 200001 1 0 9.2 1.61 250 19 INV DOD01 247925 19991115 16.5 2.89 19.39 22 Nov 99 200001 1 0 16.5 2.89 250 20 INV DOD01 248121 19991116 123.96 21.69 145.65 22 Nov 99 200001 1 0 123.96 21.69 250 <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> </tbody>

<tbody>
</tbody>

 

[Joe4]

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"?

 

[i_malc]

<tbody> </tbody> AO1	AP1
1-Feb-06	01-Dec-19

<tbody>
</tbody>

 

[Joe4]

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.

 

[i_malc]

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.

 

[Joe4]

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.

 

[i_malc]

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?

 

[Joe4]

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.