Selecting columns using offset when there are hidden rows.

Using Excel 2016 365 ProPlus. Table with over 100,000 rows which contains approximately 2000 duplicates. Trying to delete duplicates based on information that is not in the column that indicates the duplicates. Have tried cycling through the table using "For Each C in Range ("U2:U" & lastrow).SpecialCells(xlCellTypeVisible) and then using Offset to obtain the required selection crieteria that is in other columns (could be in either of the two duplicated rows). Offset seems to be able to offset rows but not columns. Also do not understand the interaction between ActiveCell and Offset in this situation (hidden rows).

Not an experienced programmer.

Thanks in advance,

jarummel

Thanks for the quick response. I've used offset successfully for several problems but have not tried when working with hidden rows. I started by using conditional formatting to indicate the duplicate rows (always just two). I then sorted the table to get the duplicates in adjacent rows. I then filtered by color to separate the duplicates from the other table data. Interesting note is that I could not get this to work until I found a very subtle Excel limitation that color sorting will not work if the first color row is beyond 10000 rows. The data I need to determine which of the two rows to delete is in a column that does not include the duplicate values. I now have the duplicates separated from the non-duplicated rows and in logical row order. It appears that offset is offsetting to the hidden rows instead of just the non-hidden rows.


The following snippet of code is where I'm experiencing the issue

For Each C in Range("U2:U" & lastrow).SpecialCells(xlCellTypeVisible) ' The Next does move to a new visible row
rowcounter = rowcounter + 1
CellColor = C.DisplayFormat.Interior.ColorIndex ' (works)
If(cellColor = 38) Then ' Detects duplicate row
MLSOne = ActiveCell.Value
C.Offset(1,0).Select '
MLSTwo = ActiveCell.Value
C.Offset(-1,0).Select ' trying to get back to original duplicate row - does not[email protected] work
C.Offset(0,19). ' Move to column that has deletion criteria -
statusOne = ActiveCell.Value
C.Offset(1,0).Select
statusTwo = ActiveCell.Value
C.Offset(-1,-19).Select ' want to move back to original 1st duplicate row
rowindex = (Trim$((Str$(rowcounter)))
If (MLSOne = MLSTwo) Then......CHecks for real duplicate and then moves to code to determine which row to delete

Thanks for any comments/help,

jarummel

Offset on visible cell only won't work, as offset is with cell context and does it regardless of visible state.

One way to do it is... use For loop to loop until cell height > 0. But this is very inefficient way to do things.
Ex:

Please Login or Register  to view this content.

If you upload sample file that represents your data set (sanitized) and exactly what should be the output/result...

We can probably help you code something more efficient.

CK76,

Thank you very much for the response. I was afraid that Offset was going to be the limiting factor. I like your recommendation of cell height and I will give it a try. I tried a similar approach using cell color to eliminate non-duplicates while using a full search of 100,000 plus records. Was very slow and that was why I was looking for a better approach. As I mentioned, I also had tried copying the duplicates to a new sheet (not including the underlying non-duplicates) but too many complications to integrate back into my main file. These are real estate transactions that I'm trying to keep up with on a monthly basis. Very difficult to sanitize these data, but thank you for the offer of looking at a better approach. By doing this on a monthly basis I have to track when the status of a property changes (e.g., from "Active" to "Sold"), thus the multiple duplicates.

Thanks again, I'll let you know how the cell height approach works. I'll also look whether I can create a sanitized sub-set for further analysis.

jarummel

CK76,

After a lot of time trying different approaches, I went back to an earlier one that I had thought that worked. First, the conditional formatting is selected for duplicates. Second, I sort the table on the field (numeric) that has the duplicates. This is where I thought it wasn't working. Both the sort and the Excel program said they were not responding. Well, I had to leave the computer and when I came back (several hours later), it had sorted. I did some testing and it took approximately 30 minutes to complete. Because everything else has been so fast, I did not think to wait. My HP All-In-One does not have a disk status light so could not tell whether application had stopped. Then I sorted on color. Previously, I received the same "not responding" error messages. So, I let it sit and after about 15 minutes the sort came back with the ability to select the color and put on top. It took about 45 minutes to complete. I'm running Excel Pro Plus with 16GB on a 7th generation i7 processor and everything up to this point has been unbelievably fast (even when working many sheets, two large tables connected with a data model, and over 100,000 rows). With the speed I have been experiencing I never thought to wait. But the secret to bypass the hidden rows was to not use the filter function and just use sorting. BYW, I was able to use cell height but still ran into a problem with the overall approach.

So, problem solved. Many thanks for taking the time and providing recommendations.

jarummel