 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
Copy the code in a Standard module of your workbook, if you just
started with VBA see this page.
Where do I paste
the code that I find on the internet
You find different examples on this part of the page to loop through the
rows and check If a specific value exists in a column (A is the column in my
example) and delete that row if it exists.
The example macro loop
through all the rows in the UsedRange.
This is the first row with data
till the last row with data on your worksheet.
It can be slower if
the column that you check doesn’t use so many rows or if your UsedRange is
bigger then your data. You can find more information on Debra Dalgleish's
website about this.
http://www.contextures.on.ca/xlfaqApp.html#Unused
You can replace this part of the macro
Firstrow = .UsedRange.Cells(1).Row
Lastrow =
.UsedRange.Rows(.UsedRange.Rows.Count).Row
With this if you
want to fill in the Firstrow and the Lastrow yourself.
Firstrow = 4
Lastrow = 100
Or with
this if you want to fill in the Firstrow and let the code find the Lastrow
in the column.
Firstrow = 2
Lastrow =
.Cells(.Rows.Count, "A").End(xlUp).Row
The code example below will delete every row in the usedrange with "ron"
in the A column.
If .Value = "ron" Then
.EntireRow.Delete
I use the A column in my example, change the
A to your column in this code line.
With
.Cells(Lrow, "A")
Note: read the tips below
the macro if you want to do something different.
Sub Loop_Example()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
'We use the ActiveSheet but you can replace this with
'Sheets("MySheet")if you want
With ActiveSheet
'We select the sheet so we can change the window view
.Select
'If you are in Page Break Preview Or Page Layout view go
'back to normal view, we do this for speed
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
'Turn off Page Breaks, we do this for speed
.DisplayPageBreaks = False
'Set the first and last row to loop through
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = Lastrow To Firstrow Step -1
'We check the values in the A column in this example
With .Cells(Lrow, "A")
If Not IsError(.Value) Then
If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron"
'in Column A, case sensitive.
End If
End With
Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
The example macro above will delete rows with "ron" in the A column now.
If .Value = "ron" Then .EntireRow.Delete
'This will delete each row with the Value "ron" in Column A, case sensitive.
You can replace the two lines above with one of the examples below.
Note: I use text examples like ="ron" but you can also use
= 1200 for numeric columns or use >, <, >=, <=.
Tip:
If you want to delete all rows that not have "ron" in column A then replace
= for <>
If .Value Like "*ron*" Then
.EntireRow.Delete
'This will delete each row where "ron" is a part
of the string, case sensitive.
' Use "ron*" for a value that start with
ron or "*ron" for a value that ends with ron
If
LCase(.Value) = LCase("ron") Then .EntireRow.Delete
'This will
delete each row with the Value "ron" in Column A, not case sensitive.
If Trim(.Value) = "ron" Then .EntireRow.Delete
'It uses the Trim function to strip the spaces
'This will delete each row
with the Value "ron" in Column A, case sensitive.
If Trim(LCase(.Value)) = LCase("ron") Then
.EntireRow.Delete
'It uses the Trim function to strip the spaces
'This will delete each row with the Value "ron" in Column A, not case
sensitive.
If .Value = "" Then
.EntireRow.Delete
'This will delete each row if the cell is empty
or have a formula that evaluates to ""
If
IsEmpty(.Value) Then .EntireRow.Delete
'This will delete the row
if the cell is empty
Select Case .Value
Case
Is = "jelle", "ron", "dave": .EntireRow.Delete
End Select
'Use
Select Case if you want to check more values in the cell
If Not IsError(Application.Match(.Value, _
Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
'Or use
this one with Application.Match if you want to check more values in the
cell. You can also use a 'range with the values to delete. Replace
Array("jelle", "ron", "dave") with
Sheets("Sheet1").Range("A1:A200")
Tip: To make it
easier to edit you can define a variable to hold that array
ArrNames =
Array("jelle", "ron", "dave")
If Not IsError(Application.Match(.Value,
ArrNames, 0)) Then .EntireRow.Delete
Do not forget to add this dim
line on top of the macro if you use this Dim ArrNames
As Variant
Replace this part of the macro with one of the code examples below
'We check the values in the A column in this example With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value = "ron" Then .EntireRow.Delete 'This will delete each row with the Value "ron" 'in Column A, case sensitive. End If End With
Replace the code above with the code below
If .Cells(Lrow, "A").Value = "ron" And _
.Cells(Lrow, "B").Value = "dave"
And _
.Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
'Use
this if you want to check for values in other columns also.
If Application.CountA(.Rows(Lrow)) = 0 Then
.Rows(Lrow).Delete
'This will delete the row if the whole row is
empty (all columns)
If
Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "E"))) = 0 Then
.Rows(Lrow).Delete
'This will delete the row if the first 5 cells
in the row are empty
If
Application.CountA(.Cells(Lrow, 1).Range("A1,M1,X1")) = 0 Then
.Rows(Lrow).Delete
'This will delete the row if the cells in A, M
and X in the row are empty
If
Application.CountIf(.Rows(Lrow), "ron") = 0 Then .Rows(Lrow).Delete
' Delete each row if the value "ron" not exist in the row (It will look in
the whole row)
If
Application.CountIf(.Rows(Lrow), "ron") > 0 Then .Rows(Lrow).Delete
' Delete each row if the value "ron" exist in the row (It will look in the
whole row)
Tip: With CountIf you can use wild cards
like this "*ron*" (is also working then if "ron" is a part
of a cell)
Use "*ron" for a value that start with ron or
"ron*" for a value that ends with ron. See the Excel help
for more information about CountIf, there are examples there about other
wildcards like ?.
There are many more ways to delete rows with VBA, I add a few more examples in a text file on my site. the code in the TXT file will be faster if you have a lot of data.
Open the TXT file with more examples