Solved: Deleting rows based on column content.

[0mar_little]

Hello,

In a sheet like the one below:

....A......B.....C
1 user1 $7 France
2 user2 $8 Canada
3 user3 $5 Italy
4 user4 $8 France
5 user5 $3 France

I want to create a code for a macro that scans for the column C, checks which users are NOT from France, and then delete the respective entire row.

Basically I want to delete the users that aren't from France and move up the remaining users to the empty rows
If the moving part is too complicated that's ok, I'm mostly interested in filtering the users.

Thanks in advance.

[Trebor76]

Hi Omar_little,

Welcome to the forum!!

As the following will potentially delete row(s), please initially try it on a sample of your data in case the results are not as expected:

[vba]Option Explicit

Sub Macro1()

'http://www.vbaexpress.com/forum/showthread.php?t=38931
'Delete any row(s) where the text in Col C is not 'France'.

Application.ScreenUpdating = False

With Intersect(Range("C:C"), ActiveSheet.UsedRange)
.AutoFilter Field:=1, Criteria1:="<>France"
If Range("C1").Value = "France" Then
Rows(1).Hidden = True
End If
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
If Rows(1).Hidden = True Then
Rows(1).Hidden = False
End If
End With

ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True

End Sub[/vba]
Regards,

Robert

[0mar_little]

Hi Trebor,

Actually I was working on something and came up with this

[VBA]Sub DeleteRow()

Dim currentRow As Integer
Dim lastRow As Integer
currentRow = 1

Range("a1").End(xlDown).Select
lastRow = ActiveCell.Row

Do Until currentRow = lastRow + 1

If Cells(currentRow, 3).Value <> "France" Then
Cells(currentRow, 3).Select
Selection.EntireRow.Delete

currentRow = currentRow - 1
lastRow = lastRow - 1
End If

currentRow = currentRow + 1

Loop

End Sub[/VBA]

But your code is much much faster.
I like coding the way I did, because I'm actually learning some programming in some languages, but your way is much more effective in this case. I will use it instead of mine.

Thanks for the help.

[Trebor76]

But your code is much much faster

Yes, deleting in one go as opposed to looping through each row will be, especially for large datasets.

I'm glad VBA Express Forum was able to provide you with an agreeable solution.

Regards,

Robert