Macro Tweek: Delete Blanks Used Range Only

[shane_aldrich]

Hi All,

Needing some help...I currently work with a raw data file with approx 300K rows... For a while, in column AY there were some blanks within the used range, so I used the macro below to delete rows with the blanks.

Here's the problem, now there are no blanks in the used range so when the filter is applied, it actually deletes every row even those with data. Of course from there I get run-time errors...

Here's the question, is there a way to search for/count blanks within the used cells, possibly based on a count for the number of used rows in column A or something?





'Remove Blanks
With ActiveSheet
'Count "" errors on AY:AY
If Application.WorksheetFunction.CountIf(.Range("AY:AY"), "") > 0 Then
'N/A found: Filter the range:
Rows("1:1").Select
Selection.AutoFilter
.Range("$A:$BE").AutoFilter Field:=51, Criteria1:="="
'Delete the rows displayed:
Application.DisplayAlerts = False
.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
'Close Autofilter
.Range("$A:$BE").AutoFilter

 

[AlphaFrog]

This will test if column AY has blanks within the used range

'Count "" errors on AY:AY
If Application.WorksheetFunction.CountIf(.Range("AY:AY"), "") > 0 And _
   [COLOR="Red"]Application.CountBlank(Intersect(Range("AY:AY"), .UsedRange)) > 0[/COLOR] Then

 

[shane_aldrich]

Thank you...that worked perfect