Forum Discussion
Solved
Can removal of certain rows be automated?
Hi, I will be getting an Excel file with some 1000 rows and 10 columns. The rows that have a value of N1 in column F and a value of N2 or N3 in column H should be removed (predicted outcome: abo...
- Yes.
You can load with any sql client.
i.g.
https://support.microsoft.com/en-us/office/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e
ms query
or
https://e.anyoupin.cn/EData/?
online sql
Automated Method Using VBA
If you're willing to try a bit of VBA, follow these steps:
Open your Excel file.
Open the VBA Editor:
Press Alt + F11 to open the VBA Editor.
Insert a New Module:
In the VBA Editor, go to Insert > Module.
Copy and Paste the VBA Code:
Copy the following code and paste it into the module window:
vba
Copy code
Sub RemoveRowsBasedOnConditions()
Dim ws As Worksheet
Dim i As Long
' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Loop from the bottom to the top to avoid skipping rows
For i = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If ws.Cells(i, "F").Value = "N1" And _
(ws.Cells(i, "H").Value = "N2" Or ws.Cells(i, "H").Value = "N3") Then
ws.Rows(i).Delete
End If
Next i
End Sub
Run the Macro:
Close the VBA Editor.
Press Alt + F8 to open the Macro dialog box.
Select RemoveRowsBasedOnConditions and click Run.
Explanation of VBA Code
ws.Cells(ws.Rows.Count, "A").End(xlUp).Row: This finds the last row with data in column A.
For i = ... To 2 Step -1: This loop goes from the last row up to the second row (assuming the first row is headers), to avoid skipping rows when deleting.
If ws.Cells(i, "F").Value = "N1" And (ws.Cells(i, "H").Value = "N2" Or ws.Cells(i, "H").Value = "N3"): This checks the conditions in columns F and H.
ws.Rows(i).Delete: This deletes the row if the conditions are met.
This VBA script will automatically remove rows that meet the specified conditions, leaving you with the filtered dataset.
If you're willing to try a bit of VBA, follow these steps:
Open your Excel file.
Open the VBA Editor:
Press Alt + F11 to open the VBA Editor.
Insert a New Module:
In the VBA Editor, go to Insert > Module.
Copy and Paste the VBA Code:
Copy the following code and paste it into the module window:
vba
Copy code
Sub RemoveRowsBasedOnConditions()
Dim ws As Worksheet
Dim i As Long
' Set the worksheet
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Loop from the bottom to the top to avoid skipping rows
For i = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row To 2 Step -1
If ws.Cells(i, "F").Value = "N1" And _
(ws.Cells(i, "H").Value = "N2" Or ws.Cells(i, "H").Value = "N3") Then
ws.Rows(i).Delete
End If
Next i
End Sub
Run the Macro:
Close the VBA Editor.
Press Alt + F8 to open the Macro dialog box.
Select RemoveRowsBasedOnConditions and click Run.
Explanation of VBA Code
ws.Cells(ws.Rows.Count, "A").End(xlUp).Row: This finds the last row with data in column A.
For i = ... To 2 Step -1: This loop goes from the last row up to the second row (assuming the first row is headers), to avoid skipping rows when deleting.
If ws.Cells(i, "F").Value = "N1" And (ws.Cells(i, "H").Value = "N2" Or ws.Cells(i, "H").Value = "N3"): This checks the conditions in columns F and H.
ws.Rows(i).Delete: This deletes the row if the conditions are met.
This VBA script will automatically remove rows that meet the specified conditions, leaving you with the filtered dataset.
Thank you very much for your extensive reply. I labeled the other answer(s) as Best Answer because the SQL way is much easier for me. But it was very kind of you.
