Workaround For Limit To Working With Noncontiguous Visible Cells

[Wookiee]

I have created a macro for some co-workers to use which helps them clean up and format some daily reports which originate from text files. They still have to import the files from the raw text file by manually setting delimiters and removing superfluous columns, but once they get it open in Excel, my macro has enabled them to save about 15 minutes per day by automating the cleanup of the files. These files usually have thousands of rows and it's not possible to determine how many will appear each day.

Since I created the macro, the large number of rows hasn't presented a problem, but today there were about 90K rows and my macro kept throwing errors due to the fact that I use a method of filtering, then deleting or updating visible cells based on the filter criteria. I modified my code to try and handle these situations, but VBA seems to stop no matter what I do whenever I attempt to use SpecialCells(xlCellTypeVisible) on a range of more than 8,192 noncontiguous rows.

I've attempted a number of permutations of the codes below, but have as of yet been unable to devise a workaround that works.

Private Sub Delete_Noncontiguous_Visible_Cells()

On Error GoTo TheHardWay
Selection.SpecialCells(xlCellTypeVisible).EntireColumn.Delete
Exit Sub

TheHardWay:
For Each rngCell In Selection.SpecialCells(xlCellTypeVisible)
    rngCell.EntireColumn.Delete
Next rngCell

End Sub

Private Sub Noncontiguous_1s()

On Error GoTo TheHardWay
Selection.SpecialCells(xlCellTypeVisible) = "1"
Exit Sub

TheHardWay:
For Each rngCell In Selection.SpecialCells(xlCellTypeVisible)
    rngCell = "1"
Next rngCell

End Sub

PS. I know that I could sort the rows so that my filter criteria chooses a range of contiguous rows, but the data needs to be preserved in the sequence it appears in the original text file. I also imagine a looped structure could probably work, but I'd prefer to avoid that option if possible.

 

[rallcorn]

You might check out Rick Rothstein's code in
http://www.mrexcel.com/forum/excel-questions/662648-visual-basic-applications-delete-entire-row-if-col-b-c-row-display-p.html?highlight=vba+delete+entire+ros+col+a%2Cb%2Cb+row+display+P

You should be able to adjust this to fit your situation.

 

[Wookiee]

You might check out Rick Rothstein's code in
http://www.mrexcel.com/forum/excel-questions/662648-visual-basic-applications-delete-entire-row-if-col-b-c-row-display-p.html?highlight=vba+delete+entire+ros+col+a%2Cb%2Cb+row+display+P

You should be able to adjust this to fit your situation.

Thanks for the link, Rallcorn. I'm afraid, however, that I couldn't find anything in the sample codes that seemed to address the limitations imposed when the number of noncontiguous visible rows exceeds 8,192.

 

[shg]

Why not sort to get contiguous rows, delete, then sort back?

 

[rallcorn]

ADVERTISEMENT

Thanks for the link, Rallcorn. I'm afraid, however, that I couldn't find anything in the sample codes that seemed to address the limitations imposed when the number of noncontiguous visible rows exceeds 8,192.

Hmmmmm -- it deletes 17K non-contiguous rows in a snap!

Input 1-20 in rows 1-20 & copy it down to row 20K & then run the code to delete Col A rows that aren't 3, 9 or 18. I realize this isn't exactly what you were trying to do, but thought you might be able to adapt to fit your needs.

Sub DeleteRows()
    Dim UnusedColumn As Long, LastRow As Long
    
    LastRow = Cells.Find(What:="*", searchorder:=xlRows, _
        searchdirection:=xlPrevious, LookIn:=xlValues).Row
    
    UnusedColumn = Cells.Find(What:="*", searchorder:=xlByColumns, _
        searchdirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
    
    With Cells(1, UnusedColumn).Resize(LastRow)
        .FormulaR1C1 = "=if(or(RC1=3,RC1=9,RC1=18),"""",""X"")"
        .Value = .Value
        On Error Resume Next
        .SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
    End With
MsgBox "Done!"

End Sub

 

[Wookiee]

Why not sort to get contiguous rows, delete, then sort back?

Well, my first reason not to do that was because the data needs to be preserved in the sequence it appears in the original text file. I did briefly consider adding a helper column so that I could capture the original sequence of rows but I was avoiding it because I have several series of filter/deletes and filter/updates using different columns based on different criteria. I was thinking I would have to re-sort after each sequence, but maybe I can step back, take a look at the overall process and figure out a way to minimize the number of times the macro sorts the data.

Thanks for the suggestion. Sometimes I think it's better to see someone suggest a path I've already considered and discarded in my mind, if only because putting the problem into words can help illuminate factors I hadn't considered.

 

[Wookiee]

ADVERTISEMENT

Hmmmmm -- it deletes 17K non-contiguous rows in a snap! I realize this isn't exactly what you were trying to do, but thought you might be able to adapt to fit your needs.

I have to admit that, while I read Rick's thread twice, I didn't create a test file to step through so that I could see the macro in action. I'll do that and see if I can figure out how to make it work in my scenario.

Thanks!

 

[Wookiee]

Hmmmmm -- it deletes 17K non-contiguous rows in a snap! Input 1-20 in rows 1-20 & copy it down to row 20K & then run the code to delete Col A rows that aren't 3, 9 or 18. I realize this isn't exactly what you were trying to do, but thought you might be able to adapt to fit your needs.

Sub DeleteRows()
    Dim UnusedColumn As Long, LastRow As Long
    
    LastRow = Cells.Find(What:="*", searchorder:=xlRows, _
        searchdirection:=xlPrevious, LookIn:=xlValues).Row
    
    UnusedColumn = Cells.Find(What:="*", searchorder:=xlByColumns, _
        searchdirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
    
    With Cells(1, UnusedColumn).Resize(LastRow)
        .FormulaR1C1 = "=if(or(RC1=3,RC1=9,RC1=18),"""",""X"")"
        .Value = .Value
        On Error Resume Next
        .SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
    End With
MsgBox "Done!"

End Sub

I finally got a chance to test this out yesterday and, after I'd stepped through it and figured out how it did what it was doing, I was impressed by the potential for me to put this into action. But to indulge my curiosity, I extended the data to 60K rows instead of 20K. While the macro worked fine on 20K rows, when I ran it on a worksheet with 60K rows, it wound up deleting everything; I had a blank worksheet once it finished. I even tried it both with and without the On Error Resume Next lines just in case there had been an error but I wasn't seeing it due to that. Any ideas what might cause it to behave in such a manner?

Thanks!

 

[rallcorn]

I was impressed by the potential for me to put this into action.

I know - Rick is a genius! It runs much more quickly than a loop & I've utilized parts of it many, many times since discovering Rick's post.

But to indulge my curiosity, I extended the data to 60K rows instead of 20K. While the macro worked fine on 20K rows, when I ran it on a worksheet with 60K rows it wound up deleting everything

You know, I had run into that myself, but had totally forgotten about it - sorry about that!

When I encountered the problem, I tested it and it works fine up to approx 24K rows. I coded a workaround solution - but I think I'll PM Rick Rothstein to see why this occurs and whether he knows of a solution. I'll ask him to put his repsonses on this thread.

 

[shg]

The SpecialCells method fails spectacularly when the result would returns > 8192 areas -- it returns the entire range.

You can write an outer loop that limits the scope to 16K rows at a time -- but I'd still sort, delete, and re-sort.