Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF VBA
Hello - I wanted to incorporate a COUNTIF in VBA (not onto the spreadsheet) that
will precede this code. I would like the COUNTIF to first assess whether an instance of MISC appears in the cells of column 14. If TRUE, then proceed with the code. If FALSE, then skip this code and continue with the next step in the routine (does not exit the routine - there are further routine steps to follow). Thank you very much in advance! Worksheets("Check").Range("A1").AutoFilter Field:=14, Criteria1:="MISC" Application.DisplayAlerts = False ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete Application.DisplayAlerts = True ActiveSheet.ShowAllData |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF VBA
Hi,
Am Thu, 3 Mar 2016 13:23:21 -0800 (PST) schrieb Merritt Ave: Worksheets("Check").Range("A1").AutoFilter Field:=14, Criteria1:="MISC" Application.DisplayAlerts = False ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete Application.DisplayAlerts = True ActiveSheet.ShowAllData try it this way: Worksheets("Check").Range("A1").AutoFilter Field:=14, Criteria1:="MISC" Application.DisplayAlerts = False ActiveSheet.Range("A2:A1000").SpecialCells(xlCellT ypeVisible).Delete Application.DisplayAlerts = True ActiveSheet.ShowAllData Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF VBA
On Friday, 4 March 2016 02:35:09 UTC-5, Claus Busch wrote:
Hi, Am Thu, 3 Mar 2016 13:23:21 -0800 (PST) schrieb Merritt Ave: Worksheets("Check").Range("A1").AutoFilter Field:=14, Criteria1:="MISC" Application.DisplayAlerts = False ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete Application.DisplayAlerts = True ActiveSheet.ShowAllData try it this way: Worksheets("Check").Range("A1").AutoFilter Field:=14, Criteria1:="MISC" Application.DisplayAlerts = False ActiveSheet.Range("A2:A1000").SpecialCells(xlCellT ypeVisible).Delete Application.DisplayAlerts = True ActiveSheet.ShowAllData Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional Hi Claus - thanks for your response. If I use your suggested method, it will give a VBA error when there is NOT an instance of the criteria search - NO CELLS WERE FOUND. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
COUNTIF VBA
Hi,
Am Fri, 4 Mar 2016 06:23:32 -0800 (PST) schrieb Merritt Ave: If I use your suggested method, it will give a VBA error when there is NOT an instance of the criteria search - NO CELLS WERE FOUND. for me it works fine. Do you have headers? Try: Dim myCrit As String myCrit = "MISC" If Application.CountIf(Range("N1:N1000"), myCrit) 0 Then Worksheets("Check").Range("N1").AutoFilter Field:=1, Criteria1:=myCrit Application.DisplayAlerts = False ActiveSheet.Range("N2:N1000").SpecialCells(xlCellT ypeVisible).EntireRow.Delete Application.DisplayAlerts = True ActiveSheet.AutoFilterMode = False End If or: Const myCrit = "MISC" If Application.CountIf(Range("N1:N1000"), myCrit) 0 Then Worksheets("Check").Range("N1").AutoFilter Field:=1, Criteria1:=myCrit Application.DisplayAlerts = False ActiveSheet.Range("N2:N1000").SpecialCells(xlCellT ypeVisible).EntireRow.Delete Application.DisplayAlerts = True ActiveSheet.AutoFilterMode = False End If Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF help | Excel Worksheet Functions | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |