Clearing rows of data from a range (B:W) if column W has a specific value

[d0rian]

My data is in columns B:W (technically B2:W25000, since there's a header row)

For each row of data, Column W is either "d" or null ("").

Only rows where the value is "d" are relevant for me and I want to delete the contents of B:W in all the other rows.

I currently use a clunky method of sorting the range by col W, shuttling the non-"d" values to the bottom, then deleting the range where the null("") values start using a few helper cells. Would prefer some VBA that just cleared the rows I don't care about. (I don't care if it just leaves those rows blank; i.e. leaving a bunch of blank rows between the lines of relevant data).

 

[Fluff]

How about

Sub DelW()
Range("W:W").AutoFilter 1, ""
Range("W2", Range("W" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).EntireRow.Delete
End Sub

 

[d0rian]

Away from my file, will try when i get back, but looking at the code, realized that maybe i didn't stress that i only want to delete the contents from the data range (B2:W25000), NOT the entire row (because there are other unrelated formulas and tables to the right of column W)...i think that code will just wipe the entire row from (col A through col ZZZ or w/e)...?

 

[Fluff]

In that case try

Sub DelW()
Range("W:W").AutoFilter 1, ""
Range("B2", Range("W" & Rows.Count).End(xlUp)).SpecialCells(xlVisible).ClearContents
ActiveSheet.AutoFilterMode = False
End Sub

 

[My Aswer Is This]

edit

 

[d0rian]

Beautiful, thank you Fluff.

 

[Fluff]

You're welcome