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]

Thanks, shg!

You can write an outer loop that limits the scope to 16K rows at a time

Yes, that's what I had done as a workaround - but it is pretty ugly!

 

[rallcorn]

Wookiee - my apologies for steering you down the wrong path. Maybe, though, you found/learned something you can use now or later!

 

[Wookiee]

No apologies necessary, Rallcorn. I appreciate all your assistance and advice and I believe that this thread will probably benefit me in the future at some point.

And since I had actually come up with a workable solution before I even attempted to play around with Rick's macro, this was a chance for me to explore some different methodology.

Cheers!