Help creating macro that applies filters to area relative to active cell

Very much a macro noob here, spent all day on this problem and can't seem to find a solution to it
I've got the following spreadsheet
graph picture.PNG
Now what I want to do is create a macro which removes all the white space from an individual column, showing only the numbers other than zero and their corresponding dates
The final table after doing this for one column looks like this
Filter table example.PNG
I did this by simply unticking 'blank' when I opened the filter menu and it somehow worked
Now however, I want to make it so that whenever I click on one of the cells on the 'data' row and press a macro button, it will do the exact same to that column of data, I've tried making this a macro by performing this process with 'use relative references' on, however excel always writes the VBA code for it as -
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Range("$F$9:$F$1004").AutoFilter Field:=1, Criteria1:="="
ActiveSheet.Range("$F$9:$F$1004").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Sheets.Add After:=ActiveSheet
ActiveCell.Offset(7, 5).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Select
End Sub
So the big problem that I can see right now is that the range for the active sheet thing is in absolute terms, while everything else is in relative terms, I'm not entirely sure how to change this or what I should be doing differently
The closest thing I've thought of is the particularly hamfisted approach of changing the filter code to -
ActiveSheet.Range(ActiveCell.Offset(1, 0).Range("A1:A1000")).AutoFilter Field:=1, Criteria1:="="
ActiveSheet.Range(ActiveCell.Offset(1, 0).Range("A1:A1000")).AutoFilter Field:=1, Criteria1:="<>"
However this isn't working and debugger says that these lines are the reason why
Anyone have any idea what I could do here?
Apologies if this is something you learn to fix in lesson one of vba academy, I'm rather clueless as to what I'm doing, never coded before or anything even remotely similar.
Thank you very much

Is the outcome of this what you have in mind?

Please Login or Register  to view this content.

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

This code works for that column, but there's a few issues
It doesn't work for the second column, so for example it works for column F, but if you have another column at J for example it doesn't perform the same task, it tells me to 'select a cell within the data range'.
Also, it deletes everything on that sheet when you run it, only leaving the relevant dates and associated numbers, there's some data above my screenshot that I probably should have shown but I need to keep that there, I only want the macro to alter stuff below the 'date' row
Also, it isn't reversible, for some reason I can't even ctrl + z my way out of it, I need to be able to click a button (for example adding a bit onto the macro that automatically removes all filters on the 'date' row after it's finished giving this data so the sheet returns to how it was before the button was pressed.
Thanks for helping, I don't really know what I'm doing here so this is really appreciated!

Originally Posted by AlphaFrog

Please Login or Register  to view this content.

Thank you very much for this, it works extremely well!!!