SOLVED

Can removal of certain rows be automated?

Copper Contributor

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: about half the rows will remain). Can that be automated and if so, how? 

 

Please note that I have only basic working knowledge of Excel and none of Macros if applicable, so please explain step by clear step. Thanx in advance. 

7 Replies
sql,

Select * from sheet1 where not (f6 like 'N1' or f8 like 'N2' OR f8 like 'N3')
Do you mean that I first have to load the Excel file into an SQL data table and then with a query get the records that match the inclusion criteria?
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.
best response confirmed by Frankie190062 (Copper Contributor)

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.

Thanks much, even though I prefer the SQL Online service from https://sqliteonline.com/ over one that runs on a Chinese server. If one clicks Import, .csv files are also accepted. Doesn't get easier than that. :) 

Thanks for your feedback.
Yes,Online service from https://sqliteonline.com/  looks like easier.

1 best response

Accepted Solutions
best response confirmed by Frankie190062 (Copper Contributor)