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

[Bernie Deitrick]

Make your table the size of your data by changing

Sub STEP2()
'
' STEP2 Macro
'

'
    Range("A6:M7").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$6:$M$7"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = ""
    ActiveSheet.ListObjects("Table1").Resize Range("$A$6:$M$800")
    Range("A6:M7").Select
End Sub

to

Sub STEP2()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("A6", Cells(Cells(Rows.Count, "A").End(xlUp), "M")), , xlYes).Name = _
        "Table1"
End Sub

[airedale360]

HI Bernie,

I just ran your code. what is did was create a large table down to row 80115 it wrote in my formulas but did not carry the data over from "RAW". when i double clicked my one row in the table that has data is did auto-fill but did not adjust the table size to row 543. thoughts?

[MarvinP]

Hi Airedale,

Your problem sounds like a prefect one for learning and using Power Query. It can easily remove blank rows from a csv file for each new one your get daily or weekly.

It is a Microsoft Add-In for 2010 and 2013 and comes with 2016 but renamed to "Get & Transform".
Install it from
https://www.microsoft.com/en-us/down...6-af05a940c4d4

http://www.bing.com/videos/search?q=...20D2&FORM=VIRE

[airedale360]

Hi Marvin.

the add-on is a great option if I am the one running the reports, but I am not. I created this Macro for someone else to use. this means i have to account for multiple human factors on how they work in excel. this is the reason i use Paste with value to eliminate a step for them

[Bernie Deitrick]

It is hard to trouble shoot with just code - if your sheet at the time of the macro running only has one block of data starting in cell A1, then is should have only found the 500 or so entries that you have.

How about this:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A6").CurrentRegion, , xlYes).Name = _
        "Table1"

This assumes that your headers are in row 6, and the table is separated from other entries by at least one entirely blank row and column.

[airedale360]

So how its processed. The header is always on row 6. i apply formulas to each cell A7:M7 in STEP1. the in STEP2 I highlight A6:M7 and convert to a table. then resize the table to 800 rows. the data from my RAW sheet auto-fills all the way down to row 800. this week only 546 rows from "RAW" were downloaded from the csv, but my table in "clean Up" carries the formula all the way to row 800. i need the table to resize up to row 546 for this week but next week it may need to shrink to row 650.

the new code you provided now only creates a table size of A6:M7.

if this is a factor some of my data from the csv do not have something in each cell. some are blank for that row.

[Bernie Deitrick]

Where are your data if you have formulas in the new table?

You need to count your data wherever it is and use that count to adjust your table size:

lngDataCount = Worksheets("Sheet With Data").Cells(Rows.Count,"A").End(xlUp).Row

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A6:M" & lngDataCount), , xlYes).Name = _
        "Table1"

[airedale360]

that worked. i have to make this the very first thing the macro does. thank you!

[Bernie Deitrick]

Great - just make sure that you adjusted for the 6 row offset of your table. For example, if your RAW sheet starts with data in row 1, and your first row of formulas is in row 7, then you need to add 6 to the count of rows.

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A6:M" & lngDataCount + 6), , xlYes).Name = _
        "Table1"