Macro that removes empty rows within a table

[airedale360]

Hello,

I have a Macro that runs once a week from data off a csv file. There is no set amount of data that is pulled from the csv. its whatever was processed that week. the csv file could have 800 rows or up to 1000 rows of data. here is my issue. I formatted my macro to create a table so i can add specific formulas to the cells to eliminate drag the formulas down. once done i copy/paste with values to remove the formulas. once that step is done this entire sheet is copied into a new excel workbook away from the macro for more manipulation.

When i filter Column A to remove blanks in the table it copies over just the filtered data and makes new issues on the next workbook. i added a recording in STEP6_1 to filter by blanks and clear content, but now if my data goes to row 545 and deletes the rest. even though i pulled 735 rows from the csv.

i try go to special to locate blanks instead but is does not consider the empty cells in the table as blanks, so i cant use that in my recording either.

here is what i need.

After i have the entire table pasted with values, the recording would then search the last cell in column A that has actual data and delete everything below. if creating a table that grows or shrinks is best let me know. i am still new to coding so bare with me on my macros.

here is my macro. this happens in STEP6_1
macro.txt

running office 2016