Deleting/Hiding Rows in Excel

[sbev]

I have a large number of worksheets with 20,000 - 40,000+ rows of data. Irregularly scattered on a sheet are unwanted rows signaled by cell Dxx being blank. How can I scan through a sheet picking out the unwanted rows and deleting or hiding them? Any help appreciated.

 

[SARUN]

Make a copy of your spreadsheet and try this

Sub deljunk()
Dim mRowCtr As Variant
mRowCtr = 2

While Range(&quot;A&quot; & mRowCtr).Value <> &quot;&quot;
if Range(&quot;D&quot; & mRowCtr).Value = &quot;&quot;
Range(&quot;A&quot; & mRowCtr).Select
ActiveCell.EntireRow.Delete
mRowCtr = mRowCtr - 1
End Select
mRowCtr = mRowCtr + 1
Wend
End Sub

&quot;Whereever you go there are people who need you for what you can do...&quot;

 

[sbev]

Thanks Sarun. It wouldn't work as given but I added a Then to the Range(&quot;D&quot; ... statement and changed the end select to an end if. You have saved me many hours of work,

 

[DrBowes]

This will run faster:

Replace &quot;Delete&quot; with &quot;Hidden = False&quot; to delete rather than hide.

Sub DeleteBlankDs()
Set rge = ActiveSheet.UsedRange
rws = rge.Rows.Count
For i = rws To 1 Step -1
If rge(i, 4).Value = &quot;&quot; Then Rows(i).Delete
Next i
Set rge = Nothing
End Sub

 

[sbev]

Thanks DrBowes, I'll try it out.

 

[SARUN]

Oops Sorry, I had a Select Case statement which I modified and apparently forgot to add the THEN and END IF Statement.

&quot;Whereever you go there are people who need you for what you can do...&quot;