View Full Version : Solved: Deleting rows based on column content.
0mar_little
09-06-2011, 05:52 PM
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
09-06-2011, 08:23 PM
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:
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
Regards,
Robert
0mar_little
09-06-2011, 09:04 PM
Hi Trebor,
Actually I was working on something and came up with this
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
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
09-06-2011, 09:21 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.