Remove blank rows for large data set

[artz]

Hi,

I've got a data worksheet with over 1 million rows. I need to remove blank rows so that I can chart the data. The following code worked fine for worksheets with tens of thousands of rows but Excel stops responding when the data set goes into the hundreds of thousands of rows:

Sub DeleteRowIfCellBlank()
    On Error Resume Next
 
    Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Can anyone in the Forum provide code that will work well with with a large data set? Any help is greatly appreciated.

Thanks,

Art

 

[artz]

All,

Many thanks to all who helped. Several great solutions making working with these huge files a piece of cake.

Art

 

[Peter_SSs]

All,

Many thanks to all who helped. Several great solutions making working with these huge files a piece of cake.

Art

You're welcome.

I assume that you have realised that the options that only sort do not actually remove the rows that are blank in the relevant column so data in those rows but in other columns would remain on your worksheet - though at the bottom.

 

[artz]

Yup. but it's all good, thanks. Got me where I need to go and get done. I just wonder why Excel stumbles with large arrays. Twenty years ago, I worked on arrays of data 10x this size on much less competent hardware using Matlab. That program never blinked. I think that MS should get XL up to speed for the scientific community. My two cents, and thanks again. Art.

 

[mohadin]

You are welcome
Be happy

 

[Peter_SSs]

Yup. but it's all good, thanks. Got me where I need to go and get done.

Not sure which code you ended up using & noting that speed seems to be something of an issue for you, the code that I posted that does actually remove the 'blank rows' & doesn't leave an extra column of numbers to the right so gives a 'tidier' result & to boot is about 20% faster than the code that doesn't remove the row. If you don't mind the spare data at the bottom, my code would be a tiny bit faster again if you removed the .Resize(k).EntireRow.Delete line near the end.

In the end though, which code you use is up to you & the main thing is you got the result you wanted.

I just wonder why Excel stumbles with large arrays. Twenty years ago, I worked on arrays of data 10x this size on much less competent hardware using Matlab. That program never blinked. I think that MS should get XL up to speed for the scientific community. My two cents, and thanks again. Art.

Have you investigated Power BI? If not, the 3 'Stickys' at the top of the Power BI Forum might be a place to start.