CODEWITHSUNDEEP
excelrangenamed-rangesvba
S rick
S rick

Reputation: 44

How can I delete rows from a set range object

Once I set a range variable in vba is there a way of deleting rows and/or columns cells from it based on a value?

For example:

Dim Lrange as Range

Set Lrange = Range("A1:E5")

For each row in Lrange
   If cell(Lrange.row, 3).value = "Somestring" Then
       LRange.row.delete
   End if
Next

Is this possible or would I have to store it in worksheet to manipulate the range?

Any suggestions are welcome!

Upvotes: 0

Views: 9921

Answers (2)

OldUgly
OldUgly

Reputation: 2119

If you want to delete the rows in the worksheet, based on a value, then code such as the following will do that ...

Sub DeleteRows()
Dim aRange As Range, aRow As Range, aCell As Range
    Set aRange = Range("A1:E5")
    For Each aRow In aRange.Rows
        For Each aCell In aRow.Cells
            If aCell.Value = "Somestring" Then
                aRow.EntireRow.Delete
                Exit For
            End If
        Next aCell
    Next aRow
End Sub

If you want to change the range that an object is referencing in VBA, based on a value, then code such as the following will do that ...

Sub DeleteRowsInObject()
Dim aRange As Range, aRow As Range, aCell As Range
Dim bRange As Range, found As Boolean
    Set aRange = Range("A1:E5")
    For Each aRow In aRange.Rows
        found = False
        For Each aCell In aRow.Cells
            If aCell.Value = "Somestring" Then found = True
        Next aCell
        If Not found Then
            If bRange Is Nothing Then
                Set bRange = aRow
            Else
                Set bRange = Union(bRange, aRow)
            End If
        End If
    Next aRow
    Set aRange = bRange
    Set bRange = Nothing
End Sub

You need to be careful with a Range object that is non-continuous with it's rows / columns as some of the normal properties / methods don't necessarily provide the values you would expect.

Upvotes: 0

Rory
Rory

Reputation: 34075

If you are going to delete, you should loop backwards through the rows:

Dim Lrange                As Range
Dim n                     As Long
Set Lrange = Range("A1:E5")

For n = Lrange.Rows.Count To 1 Step -1
    If Lrange.Cells(n, 3).Value = "Somestring" Then
        Lrange.Rows(n).Delete
    End If
Next

for example.

Upvotes: 2

Related Questions