[SalientAnimal]

Hi Guys/Gals,

I'm looking for a very basic marco that will delete an entire row if the cell in column U contains any text/value.

Thanks

[SalientAnimal]

Originally Posted by SalientAnimal

Hi Guys/Gals,

I'm looking for a very basic marco that will delete an entire row if the cell in column U contains any text/value.

Thanks

Found a solution:

Sub DelRows()
Application.ScreenUpdating = False
With ActiveSheet.UsedRange
.AutoFilter Field:=7, Criteria1:="=a"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
End Sub

Where .AutoFilter Field:=7, the 7 indicates the column number.

[joeu2004[_2_]]

"SalientAnimal" wrote:
I'm looking for a very basic marco that will delete an
entire row if the cell in column U contains any text/value.

Not sure what you mean by "any text/value". Do you really mean "a specific
value"? Or do you mean "not a formula and not empty"? Do you mean simply
"not empty"?

I assume you mean "a specific value".

The following will delete rows conditionally over a selected region.


Sub doit()
Const mycol = "U"
const myval = 4 ' your "specific value" (my assumption)
Dim nr As Long, i As Long
Dim rng As Range, r as Range
Application.ScreenUpdating = False
rng = Selection ' your own range
nr = rng.Rows.Count
' important: process rows in reverse when deleting
For i = nr To 1 Step -1
Set r = rng.Cells(i, 1).EntireRow
If r.Cells(1, mycol) = myval Then r.Delete
Next
Application.ScreenUpdating = True
End Sub

[Don Guillett[_2_]]

On Tuesday, July 10, 2012 5:45:23 AM UTC-5, SalientAnimal wrote:
Hi Guys/Gals,

I'm looking for a very basic marco that will delete an entire row if the
cell in column U contains any text/value.

Thanks




--
SalientAnimal

filtering faster than loop
Sub DeleteNonBlanksinColumnSAS()
'column U is colummn 21
'Assumes columns a:u have date on header row
Application.ScreenUpdating = False
With ActiveSheet.UsedRange ' or Range("a2:u20000")
.AutoFilter Field:=21, Criteria1:="<"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

[Don Guillett[_2_]]

On Tuesday, July 10, 2012 5:45:23 AM UTC-5, SalientAnimal wrote:
Hi Guys/Gals,

I'm looking for a very basic marco that will delete an entire row if the
cell in column U contains any text/value.

Thanks




--
SalientAnimal

dSub DeleteNonBlanksinColumnSAS()
'column U is colummn 21
'Assumes columns a:u have date on header row
Application.ScreenUpdating = False
With ActiveSheet.UsedRange 'Range("F2:J200")
.AutoFilter Field:=21, Criteria1:="<"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub