You are in: q&a > Question Paul

QUESTION


01/09/18 | Excel 2013/2016 | VBA | by Jorge Vilar

I receive an Excel table every month with 3 Fields (let's call them Field1, Field2 and Field3). The number of records is variable. I want to create a macro that goes to all the records and deletes the line if the value on Feld1 is either the text "Sloc" or there is no text.
This is an example from the file I receive. PaulOriginal.xlsm.
Paul

ANSWER

My first idea was to loop all the records from the top to bottom until finds an empty line. Problem is that, according to the file sent, you can have 1, 2 or more empty lines on FIeld1 and then a record.
So, my second idea was to begin from the last line (the user must select it) and loop up.

Hands-on

Let’s begin. I will create a new Excel file, with only objective to hold the macro(s).
Open Excel and press Alt+F11. This will open the Microsoft Visual Basic for Applications windows, with a VBAProject.

Let's create a new module:
Press Insert > Module. In the properties Window change the name from Module1 to basGeneral.
I'm using the Reddick VBA Naming Conventions https://ss64.com/access/syntax-naming.html .

Let's create a subprocedure in the module:
If not selected, select the code windows. Insert > Procedure.
Give the name, for instance TryOne. Add the following code:

Public Sub TryOne()
Do
ActiveCell.Offset(-1, 0).Select
Loop
End Sub

What it does?
Open the original file (link above) and select one line.

Run the macro by pressing Alt+F8 and selecting the macro TryOne.

The do loop does what it says, enters a loop. And inside the loop selects the cell one line above the selected cell (Active Cell). the syntax from Offset is:

expression.Offset(RowOffset, ColumnOffset)

You will get an error. Why? Because with the loop the cell will always be selected above the active cell and so on until it arrives at first line. After that, trying to select the line above the first line (which doesn't exist) gives error 1004.

To avoid this, we add an error trapping:

Public Sub TryOne()
On Error GoTo err_h
Do
ActiveCell.Offset(-1, 0).Select
Loop
Exit Sub
err_h:
Exit Sub
End Sub

Running this code, if an error occurs, it jumps immediately to the error handler err_h. In here I say to exit the sub, effectively stopping at line 1.

We are in the good way!
Now the idea is to keep the value of active cell and check if the value is "Sloc" or the empty value "". In either one of the previous cases, delete the entire row.

Let’s create another subprocedure. I will call it TryTwo:

Public Sub TryTwo()
On Error GoTo err_h
Dim value1 As Variant
Do
value1 = ActiveCell.Value
If (value1 = "Sloc" Or value1 = "") Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(-1, 0).Select
Loop
err_h:
Exit Sub
End Sub

First, I created a variable called value1 to hold the value of the active cell and check if it’s “Sloc” or “”. In that case, delete the entire row.

Save it, go to the last record and select the cell from first column. Press Alt+F8 and choose TryTwo.

Problem solved. Please note that after running a macro, the Undo doesn’t work, so if you want to go back to the original file, you need to close it without saving (or download the original file again!). Also, the comparison is case sensitive; if it finds text “Sloc” deletes entire row but finding “sloc” not.

- end -

Comments, suggestions and questions are always welcome - jorge@jorgevilar.com.

 •   top...»