Hello - is there a way (I'm sure there is but I'm not smart enough to figure it out) to test whether a filter vba command will result in wiping out all data?

Sub test()

'delete rows that contain the word "paper" in Column D

Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:="<*paper*"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
ActiveSheet.ShowAllData

'end of "paper" check

'start next sequence ..etc
Columns("F:F").Select
etc, etc, etc

End Sub

In other words, if the data set does not have the keyword "paper" in Column D, then the above vba commands will delete all the data.

Looking for a macro that will precede the above to check whether Column D has any instances of the word "paper" *BEFORE* running the macro. If no "paper" exists in the Column, then the sequence should be bypassed and continue to the next set of vba commands immediately after (ie. jump immediately to Selecting Column F as shown above).

Thank you in advance!

Try...

Sub DeleteRows()
Dim vData, n&, sTmp$
Const sCriteria$ = "paper"

With ActiveSheet
vData = .UsedRange
For n = UBound(vData) To LBound(vData) Step -1
sTmp = Join(Application.Index(vData, n, 0), "|")
If InStr(sTmp, sCriteria) 0 Then .Rows(n).Delete
Next 'n
End With 'ActiveSheet
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

To filter on column "D" only...

Sub DeleteRows2()
Dim vData, n&, sTmp$
Const sCriteria$ = "paper": Const lColNdx& = 4

With ActiveSheet
vData = .UsedRange
For n = UBound(vData) To LBound(vData) Step -1
If vData(n, lColNdx) = sCriteria Then .Rows(n).Delete
Next 'n
End With 'ActiveSheet
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Check this out and let me know if it works !! Regards, Mandeep baluja


Sub test()

Dim col As Long: col = ActiveSheet.Cells(1, Columns.Count).End(xlUp).Column
Dim lr As Long: lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To col

If Application.CountIfs(Range(Cells(2, i), Cells(lr, i)), "*paper*") 0 Then
Worksheets("Sheet1").Range("A1").AutoFilter Field:=i, Criteria1:="<*paper*"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
ActiveSheet.ShowAllData
End If

Next

ActiveSheet.ShowAllData


End Sub

Why would you think that might work?
The End() direction for getting the rightmost col is wrong.
There's no way to know that the rightmost col is in row 1.
The criteria is to delete only rows that contain "paper" in col "D".

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Tony,

Am Fri, 27 Nov 2015 19:38:30 -0800 (PST) schrieb Tony:

'delete rows that contain the word "paper" in Column D

Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:="<*paper*"
^^^^
if you want to delete the rows that contain paper you have to filter
with the criteria ="=*paper*"and delete the visible rows.

Try:

Sub Filter()
Dim LRow As Long, i As Long

Application.ScreenUpdating = False

With Worksheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 4 To 6 Step 2
.UsedRange.AutoFilter Field:=i, Criteria1:="=*paper*"
.Range("A2:A" & LRow).SpecialCells(xlCellTypeVisible) _
.EntireRow.Delete
.AutoFilterMode = False
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Friday, 27 November 2015 22:38:41 UTC-5, Merritt Ave wrote:
Hello - is there a way (I'm sure there is but I'm not smart enough to figure it out) to test whether a filter vba command will result in wiping out all data?

Sub test()

'delete rows that contain the word "paper" in Column D

Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:="<*paper*"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
ActiveSheet.ShowAllData

'end of "paper" check

'start next sequence ..etc
Columns("F:F").Select
etc, etc, etc

End Sub

In other words, if the data set does not have the keyword "paper" in Column D, then the above vba commands will delete all the data.

Looking for a macro that will precede the above to check whether Column D has any instances of the word "paper" *BEFORE* running the macro. If no "paper" exists in the Column, then the sequence should be bypassed and continue to the next set of vba commands immediately after (ie. jump immediately to Selecting Column F as shown above).

Thank you in advance!


I'm afraid that I did a poor job in explaining the task at hand. I will try once again and hopefully I'm clear.

Picture says a thousand words...

A B C D E F etc
1 123 ABC blah box card bow
2 456 CBA blah box card bow
3 789 DEF blah box card bow

The current macro that I presented originally performed on the following basis: delete all rows whereby there is no paper in Column D

If the data does NOT have any instance of the word paper in Column D, the macro will delete ALL rows.

If, however, row 4 is added and contains paper in Column D, then the macro would effectively delete Rows 1, 2 and 3 and leave Row 4.

Would like to know how to test whether there is any instance of the word paper in Column D BEFORE the macro is run so that the macro is bypassed to the next sequence (eg. select Column F as shown in above example). Bypassing the macro will prevent all the data from being cleared.

If I ran the macro on Rows 1, 2 and 3, all data would be wiped because there is no instance of paper in Column D.

Sorry if being redundant - just trying to be clear (which I didn't do the first time around).
Thank you again!

Merritt Ave wrote:
On Friday, 27 November 2015 22:38:41 UTC-5, Merritt Ave wrote:
Hello - is there a way (I'm sure there is but I'm not smart enough to figure it out) to test whether a filter vba command will result in wiping out all data?

Sub test()

'delete rows that contain the word "paper" in Column D

Worksheets("Sheet1").Range("A1").AutoFilter Field:=4, Criteria1:="<*paper*"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
ActiveSheet.ShowAllData

'end of "paper" check

'start next sequence ..etc
Columns("F:F").Select
etc, etc, etc

End Sub

In other words, if the data set does not have the keyword "paper" in Column D, then the above vba commands will delete all the data.

Looking for a macro that will precede the above to check whether Column D has any instances of the word "paper" *BEFORE* running the macro. If no "paper" exists in the Column, then the sequence should be bypassed and continue to the next set of vba commands immediately after (ie. jump immediately to Selecting Column F as shown above).

Thank you in advance!


I'm afraid that I did a poor job in explaining the task at hand. I will try once again and hopefully I'm clear.

Picture says a thousand words...

A B C D E F etc
1 123 ABC blah box card bow
2 456 CBA blah box card bow
3 789 DEF blah box card bow

The current macro that I presented originally performed on the following basis: delete all rows whereby there is no paper in Column D

If the data does NOT have any instance of the word paper in Column D, the macro will delete ALL rows.

If, however, row 4 is added and contains paper in Column D, then the macro would effectively delete Rows 1, 2 and 3 and leave Row 4.

Would like to know how to test whether there is any instance of the word paper in Column D BEFORE the macro is run so that the macro is bypassed to the next sequence (eg. select Column F as shown in above example). Bypassing the macro will prevent all the data from being cleared.

If I ran the macro on Rows 1, 2 and 3, all data would be wiped because there is no instance of paper in Column D.

Sorry if being redundant - just trying to be clear (which I didn't do the first time around).
Thank you again!


put
=COUNTIF(D1:D1000,"paper")
somewhere

0 means no paper.

Hey Merritt, Check this code it will check first whether the column contains an instance of Paper or not.


Sub test()

Dim col As Long: col = ActiveSheet.Cells(1, Columns.Count).End(xlUp).Column
Dim lr As Long: lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To col

If Application.CountIfs(Range(Cells(2, i), Cells(lr, i)), "*paper*") 0 Then
Worksheets("Sheet1").Range("A1").AutoFilter Field:=i, Criteria1:="<*paper*"
Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True
ActiveSheet.ShowAllData
End If

Next

ActiveSheet.ShowAllData


End Sub

Hi Merrit,

Am Sun, 29 Nov 2015 19:20:21 -0800 (PST) schrieb Merritt Ave:

Would like to know how to test whether there is any instance of the word paper in Column D BEFORE the macro is run so that the macro is bypassed to the next sequence (eg. select Column F as shown in above example). Bypassing the macro will prevent all the data from being cleared.

try:

Sub Filter()
Dim varCols As Variant, varFilter() As Variant
Dim i As Long, Res As Long, fCrit As Long, LRow As Long

'Modify to the cols you want to filter
varCols = Array("C", "D", "E", "F")

ReDim Preserve varFilter(UBound(varCols), 1)
For i = LBound(varCols) To UBound(varCols)
Res = Application.CountIf(Columns(varCols(i)), "*paper*")
varFilter(i, 0) = Asc(varCols(i)) - 64
varFilter(i, 1) = Res
Next
'Modify sheet name
With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = LBound(varFilter) To UBound(varFilter)
If varFilter(i, 1) 0 Then
fCrit = varFilter(i, 0)
.UsedRange.AutoFilter Field:=fCrit, Criteria1:="<*paper*"
.Range("A2:A" & LRow).SpecialCells(xlCellTypeVisible) _
.EntireRow.Delete
End If
.AutoFilterMode = False
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional