Combined CSV Data Connection and Filter

[gijimbo]

I have a worksheet that gets the majority of its data from a csv file that gets updated daily. I have it setup so that the workbook refreshes/re-imports the csv data when the workbook is opened.

I currently would like to be able to control the viewing of the data using excel's filter capability. This is fine except when the csv file grows larger, the range of data that is covered by the filter is only as large as when it was first created. The only way I can make it encompass all of the data is to remove the filter and re-add the filter.

I tried doing this through a macro that runs when the workbook is opened except it does not work because it runs before the csv file gets checked and the new data is added to the worksheet.

Does anyone know a better way to do this?
Maybe some method of making the filter range dynamic?
Or making the filter range dependent on the length of one of the columns somehow?

My primary goal is to have the csv update and the filter cover all the data via some automated method at startup. I can not have the users turning the filter on and off every time they open the workbook.

Any responses are appreciated.

 

[gijimbo]

Solution: (in case anyone has the same issue in the future)

The fix was pretty simple. I just had to disable the data auto-update when the workbook is opened and add the following to the beginning of my macro (which is called when the workbook is opened). This makes it so the data still updates when the workbook opens but it makes sure it's done before the macro is run.

'Refresh data file
ActiveWorkbook.RefreshAll

After the "refresh", I just used the following to re-dimension the filter range.

Range("A2").AutoFilter 'Remove current filter
Range("A2").AutoFilter 'Add new filter