nnick
08-11-2015, 10:42 AM
I am trying to use VBA to delete rows within a user selected range and user selected string with an inputbox.
But I have a problem, the code I have works but there are some instances where the user selected string is in row which contain data in column "I" that I need to keep but still clear the rest of the data in the row within columns A through H.
Does anyone know what I can add to the code to allow me to delete the entire row if column I is empty but if it's populated, delete everything in the row but the contents of column I.
Sub DeleteRowsWithinRange()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleIdRng = "Range"
xTitleIdDel = "Delete Text"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleIdRng, InputRng.Address, Type:=8)
DeleteStr = Application.InputBox("Delete Text", xTitleIdDel, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub
But I have a problem, the code I have works but there are some instances where the user selected string is in row which contain data in column "I" that I need to keep but still clear the rest of the data in the row within columns A through H.
Does anyone know what I can add to the code to allow me to delete the entire row if column I is empty but if it's populated, delete everything in the row but the contents of column I.
Sub DeleteRowsWithinRange()
Dim rng As Range
Dim InputRng As Range
Dim DeleteRng As Range
Dim DeleteStr As String
xTitleIdRng = "Range"
xTitleIdDel = "Delete Text"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleIdRng, InputRng.Address, Type:=8)
DeleteStr = Application.InputBox("Delete Text", xTitleIdDel, Type:=2)
For Each rng In InputRng
If rng.Value = DeleteStr Then
If DeleteRng Is Nothing Then
Set DeleteRng = rng
Else
Set DeleteRng = Application.Union(DeleteRng, rng)
End If
End If
Next
DeleteRng.EntireRow.Delete
End Sub