Remove extraneous rows

[h2o2sail]

I have a data table that contains rows that all have the same character set in the first column of each row and I'm looking for the best way to identify them and delete them in a macro. Some ideas I've tried are comparing the value in the first column in an if statement and marking the row for deletion. I've also tried to filter the rows in the table and delete them. The macro needs to deal with table length that changes every time it's run because the report that generates the table is based on a time period and the number of transactions varies over time.

My current code looks like this:

Please Login or Register  to view this content.

any thoughts?

[pb71]

Something along these lines perhaps:

Please Login or Register  to view this content.

[Palmetto]

h2o2sail,

Your post does not comply with the Forum Rules regarding use of code tags. All VBA code must be wrapped in code tags. Please edit your original post to add the code tags (DO NOT create a new post duplicating the original post).

Rule #3

Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button. For more information about these and other tags, click here.

Although pb71 has provided a solution, there is much more efficient method that does not involve looping through cells. After you have complied with rule about use of code tags, alternate solutions may be suggested.

[h2o2sail]

HI Palmetto,

Thanks for pointing out how to properly quote the code. You mentioned that there is a more efficient method for deleting rows that doesn't involve looping. Could you share it?

h2o2sail.

[Palmetto]

I have a data table that contains rows that all have the same character set in the first column of each row

I interpret this to mean column-A, so why would you need to loop across columns? Your code doesn't loop across columns due to not defining lcolumn (the variable is dimensioned, but the columns are not counted so the variable is not valid).

Using filtering is much more efficient than looping. In conjunction, using the SpecialCells(xlCellTypeVisible) property we can act upon all cells in the filtered range in one go.

Please Login or Register  to view this content.

[h2o2sail]

Using filtering is much more efficient than looping. In conjunction, using the SpecialCells(xlCellTypeVisible) property we can act upon all cells in the filtered range in one go.

How well does this work on a table of data that is between 400,000 and 600,000 rows? I have tried to filter the data in the table directly and don't see all the valid values as choices in the drop down menus created by autofilter.

I interpret this to mean column-A, so why would you need to loop across columns?

You are correct and I have removed that from my code since I initially posted it.

h2o2sail

[Palmetto]

How well does this work on a table of data that is between 400,000 and 600,000 rows?

Can't say exactly. I don't typically work with data sets this large. Give it try on a back up copy of the workbook and lets us know. You may have to run the code more than once.

I have tried to filter the data in the table directly and don't see all the valid values as choices in the drop down menus created by autofilter.

Autofilter has a limitation in that it only shows the first 1000 unique values in the list.

See this: Not all list items are displayed in the AutoFilter list in Excel