Elimina righe in un foglio di calcolo where <condizione> quando la colonna di <condizione> è sconosciuta

Devo trovare la parola "scaduto" e "dovuto" in un foglio di calcolo, ma la colonna in cui appariranno sarà variabile, così come il numero di record (righe). Devo eliminare tutte le righe che NON dispongono di questi valori nei dati, quindi totalizzare i dati rimasti sul foglio dopo che gli altri sono stati eliminati. Qualsiasi indizio?

Puoi provare qualcosa di simile.

Mettere questo in una macro da eseguire

Sub Macro1() Dim sheet As Worksheet Dim usedRange As Range Set sheet = ActiveSheet Set usedRange = sheet.usedRange Dim rowCount As Integer Dim columnCount As Integer Dim iRow As Integer Dim iColumn As Integer rowCount = usedRange.Rows.Count columnCount = usedRange.Columns.Count For iRow = rowCount To 1 Step -1 For iColumn = 1 To columnCount If ((InStr(1, LCase(usedRange(iRow, iColumn)), "overdue") > 0) Or (InStr(1, LCase(usedRange(iRow, iColumn)), "due") > 0)) Then usedRange.Range(Cells(iRow, 1), Cells(iRow, columnCount)).Delete End If Next iColumn Next iRow End Sub 

Potresti anche provare qualcosa con ADO.

 Dim cn As Object Dim rs As Object Dim strFile As String Dim strCon As String Dim strSQL As String Dim strWhere As String Dim i As Integer ''http://support.microsoft.com/kb/246335 strFile = ActiveWorkbook.FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [Sheet1$] AS s " rs.Open strSQL, cn, 3, 3 For i = 0 To rs.fields.Count - 1 strWhere = strWhere & " AND (UCase(s.[" _ & rs.fields(i).Name & "] ) Not Like '%DUE%' Or s.[" _ & rs.fields(i).Name & "] Is Null) " Next strSQL = strSQL & " WHERE " & Mid(strWhere, 5) rs.Close rs.Open strSQL For i = 0 To rs.fields.Count - 1 Sheets("Sheet2").Cells(1, i + 1) = rs.fields(i).Name Next Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs Dim strCon Come String Dim cn As Object Dim rs As Object Dim strFile As String Dim strCon As String Dim strSQL As String Dim strWhere As String Dim i As Integer ''http://support.microsoft.com/kb/246335 strFile = ActiveWorkbook.FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [Sheet1$] AS s " rs.Open strSQL, cn, 3, 3 For i = 0 To rs.fields.Count - 1 strWhere = strWhere & " AND (UCase(s.[" _ & rs.fields(i).Name & "] ) Not Like '%DUE%' Or s.[" _ & rs.fields(i).Name & "] Is Null) " Next strSQL = strSQL & " WHERE " & Mid(strWhere, 5) rs.Close rs.Open strSQL For i = 0 To rs.fields.Count - 1 Sheets("Sheet2").Cells(1, i + 1) = rs.fields(i).Name Next Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs & "; Proprietà estese =" "Excel 8.0; HDR = Sì; IMEX = 1" ";" Dim cn As Object Dim rs As Object Dim strFile As String Dim strCon As String Dim strSQL As String Dim strWhere As String Dim i As Integer ''http://support.microsoft.com/kb/246335 strFile = ActiveWorkbook.FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [Sheet1$] AS s " rs.Open strSQL, cn, 3, 3 For i = 0 To rs.fields.Count - 1 strWhere = strWhere & " AND (UCase(s.[" _ & rs.fields(i).Name & "] ) Not Like '%DUE%' Or s.[" _ & rs.fields(i).Name & "] Is Null) " Next strSQL = strSQL & " WHERE " & Mid(strWhere, 5) rs.Close rs.Open strSQL For i = 0 To rs.fields.Count - 1 Sheets("Sheet2").Cells(1, i + 1) = rs.fields(i).Name Next Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs cn. Apri strCon Dim cn As Object Dim rs As Object Dim strFile As String Dim strCon As String Dim strSQL As String Dim strWhere As String Dim i As Integer ''http://support.microsoft.com/kb/246335 strFile = ActiveWorkbook.FullName strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _ & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";" Set cn = CreateObject("ADODB.Connection") Set rs = CreateObject("ADODB.Recordset") cn.Open strCon strSQL = "SELECT * FROM [Sheet1$] AS s " rs.Open strSQL, cn, 3, 3 For i = 0 To rs.fields.Count - 1 strWhere = strWhere & " AND (UCase(s.[" _ & rs.fields(i).Name & "] ) Not Like '%DUE%' Or s.[" _ & rs.fields(i).Name & "] Is Null) " Next strSQL = strSQL & " WHERE " & Mid(strWhere, 5) rs.Close rs.Open strSQL For i = 0 To rs.fields.Count - 1 Sheets("Sheet2").Cells(1, i + 1) = rs.fields(i).Name Next Worksheets("Sheet2").Cells(2, 1).CopyFromRecordset rs 

Excel dovrebbe essere in grado di impegnare qualsiasi valore in una string tranne un errore. Quindi, se hai formule che restituiscono errori, potrebbe causare il tipo di disallineamento. Ecco un altro modo utilizzando il metodo Find che eviterebbe questo problema. Il ritrovamento può essere più lento che attraversa le colonne, ma se non si dispone di una quantità di dati, non si nota.

 Sub DeleteOverDue() Dim i As Long Dim rFound As Range 'Loop backward through the used range For i = Sheet1.usedRange.Rows.Count To 1 Step -1 'Should find "due" and "overdue" because of xlPart Set rFound = Sheet1.usedRange.Cells(i, 1).EntireRow.Find("due", , xlValues, xlPart) 'If it's not found, delete the row If rFound Is Nothing Then Sheet1.usedRange.Cells(i, 1).EntireRow.Delete End If Next i End Sub 

Nota: questo codice elimina i dati, pertanto si prega di utilizzarlo su una copia dei tuoi dati reali fino a quando non si sa che funziona per te.