VBA - How to delete rows whose cells contain

[TopLearner]

Hello everybody,

I hope you are well.

I have the column A with multiple cities.
A1 is the header "Location"

Then, I have the name of cities in the range A1:A200.

I also have the column B with the header "Revenue"


I would like to create a macro that delete those rows that contains "(Central)" in the "Location" column, column "A"


My attempt is

Sub LimpiarFilas()

Dim ws As Worksheet
Dim cell As Range

Set ws = Sheet1


For Each cell In Range("A:A")

If cell.Value = "(Central)" Then
cell.EntireRow.Delete

End If

Next cell

End Sub

However, it is not working as cells in column A contain either cities' names like Milan or cities' names + (Central). For instance Cordoba (Central).
Then, I would like to delete the row that contains Cordoba (Central)
Would it be something like the below?

IF cell.value = IF(Isnumber(match("(Central)",B1)),"Yes","No").Value = "Yes" Then
cell.entirerow.delete

Thanks
Sanchez

 

[Joe4]

Two things:

1. If you want to see if a value is contained anywhere in a cell, use the INSTR function.
See: MS Excel: How to use the INSTR Function (VBA)

2. When deleting rows, always work backwards. Otherwise, some may be missed, as deleting rows shifts your range up, while you are working down.

Try this code:

Sub LimpiarFilas()

Dim lr As Long
Dim r As Long

Application.ScreenUpdating = False

Sheets("Sheet1").Activate

'Find last row in column A with data
lr = Cells(Rows.Count, "A").End(xlUp).Row

'Loop through all rows
For r = 1 To lr
    If InStr(Cells(r, "A"), "(Central)") > 0 Then
        Rows(r).Delete
    End If
Next r

Application.ScreenUpdating = True

End Sub

 

[johnnyL]

Alternative approach that uses filtering instead of looping:

Sub LimpiarFilas()
'
    Dim ws As Worksheet
'
    Set ws = Sheet5
'
    With ws.UsedRange
        ws.AutoFilterMode = False                                                               '   Remove any filters
'
        .AutoFilter Field:=1, Criteria1:="*(Central)*"                                          '   Filter column A for "*(Central)*"
'
        On Error Resume Next                                                                    '   For the case when there is no visible rows
        .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete  '   Skip header row 1
        On Error GoTo 0                                                                         '   Return error handling back to Excel
'
        ws.AutoFilterMode = False                                                               '   Remove filter which leaves remaining rows
    End With
End Sub

 

[TopLearner]

Two things:

1. If you want to see if a value is contained anywhere in a cell, use the INSTR function.
See: MS Excel: How to use the INSTR Function (VBA)

2. When deleting rows, always work backwards. Otherwise, some may be missed, as deleting rows shifts your range up, while you are working down.

Try this code:

Sub LimpiarFilas()

Dim lr As Long
Dim r As Long

Application.ScreenUpdating = False

Sheets("Sheet1").Activate

'Find last row in column A with data
lr = Cells(Rows.Count, "A").End(xlUp).Row

'Loop through all rows
For r = 1 To lr
    If InStr(Cells(r, "A"), "(Central)") > 0 Then
        Rows(r).Delete
    End If
Next r

Application.ScreenUpdating = True

End Sub

Hi Joe4



Many thanks for your help.

If you do not mind, I have a few questions as I am very new to VBA.



When you use style='font-family:"inherit",serif;color:#141414'>Sheets(style='font-family:"inherit",serif;color:#E29232'>"Sheet1"style='font-family:"inherit",serif;color:#141414'>).Activatestyle='font-family:Courier;color:#141414'>
Is the same as class=prism-token>Setstyle='font-family:"inherit",serif;color:#141414'> ws style='box-sizing: border-box'>style='font-family:"inherit",serif;color:#141414'>=style='font-family:"inherit",serif;color:#141414'> Sheet1style='font-family:Courier;color:#141414'>
In this case?



Could you use 1 instead of “A” to designate the first column?

Regards
Sanchez

 

[TopLearner]

Alternative approach that uses filtering instead of looping:

Sub LimpiarFilas()
'
    Dim ws As Worksheet
'
    Set ws = Sheet5
'
    With ws.UsedRange
        ws.AutoFilterMode = False                                                               '   Remove any filters
'
        .AutoFilter Field:=1, Criteria1:="*(Central)*"                                          '   Filter column A for "*(Central)*"
'
        On Error Resume Next                                                                    '   For the case when there is no visible rows
        .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete  '   Skip header row 1
        On Error GoTo 0                                                                         '   Return error handling back to Excel
'
        ws.AutoFilterMode = False                                                               '   Remove filter which leaves remaining rows
    End With
End Sub

Thanks. What does resize do?

 

[johnnyL]

Thanks. What does resize do?

Have a look here for an explanation.

Basically resize alters the range being used.

        .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete  '   Skip header row 1

That line of code you are inquiring about looks at the rows of data that are visible at that time, it skips the first row with offset(1,0) ... the header row ... and deletes the rest of the rows that are visible at that time.

 

[Joe4]

When you use style='font-family:"inherit",serif;color:#141414'>Sheets(style='font-family:"inherit",serif;color:#E29232'>"Sheet1"style='font-family:"inherit",serif;color:#141414'>).Activatestyle='font-family:Courier;color:#141414'>
Is the same as class=prism-token>Setstyle='font-family:"inherit",serif;color:#141414'> ws style='box-sizing: border-box'>style='font-family:"inherit",serif;color:#141414'>=style='font-family:"inherit",serif;color:#141414'> Sheet1style='font-family:Courier;color:#141414'>
In this case?

I have no idea what you are asking there. It seems to have nothing to do with your original question.
If you have a separate question about styles, you should post it in a new thread.

Could you use 1 instead of “A” to designate the first column?

Are you talking about when using "Cells" to refer to ranges?
With "Cells", you can use either the column letter or column number, i.e.

Cells(r, "A")

is the same as:

Cells(r, 1)

 

[TopLearner]

I have no idea what you are asking there. It seems to have nothing to do with your original question.
If you have a separate question about styles, you should post it in a new thread.


Are you talking about when using "Cells" to refer to ranges?
With "Cells", you can use either the column letter or column number, i.e.

Cells(r, "A")

is the same as:

Cells(r, 1)

Hi Joe4,

Sorry, it was not about the styles, it is the style/format that the forum put to my query, apologies.

What I asked is that, when you use Sheets("Sheet1").Activate is the same as using Set ws = Sheet1?



Thanks about the answer to my another query about the numbers and the letters to refer to rows/columns


Thanks again,
Sanchez

 

[Joe4]

What I asked is that, when you use Sheets("Sheet1").Activate is the same as using Set ws = Sheet1?

They may eventually be used to accomplish the same purpose, but they are not doing the same thing.

Sheets("Sheet1").Activate

is simply activating Sheet1.

Set ws = Sheet1

is setting Sheet1 equal to a Worksheet variable type.
This makes referencing it easier later in your code.
You could then activate that sheet in your code like this:

ws.Activate

or use it in range references, i.e.

ws.Range("A1")

 

[TopLearner]

They may eventually be used to accomplish the same purpose, but they are not doing the same thing.

Sheets("Sheet1").Activate

is simply activating Sheet1.

Set ws = Sheet1

is setting Sheet1 equal to a Worksheet variable type.
This makes referencing it easier later in your code.
You could then activate that sheet in your code like this:

ws.Activate

or use it in range references, i.e.

ws.Range("A1")

Many thanks for all of your answers