CODEWITHSUNDEEP
vbaexcel
Dave
Dave

Reputation: 11

Conditional Delete in VBA

I am trying to piece together code to make my macro work correctly. This approach has served me well in the past but I cannot seem to adapt any code correctly.

I found the following

Sub way()

Dim Cell As Range
For Each Cell In Range("A1").CurrentRegion
    If Len(Cell) < 2 Then Cell.EntireRow.Delete
Next Cell

End Sub

I can adapt the If Len(Cell) criteria to my liking. For example = 10

I do not know how to adapt the code to make it search through all cells in column A and delete the appropriate rows. The code above only does it for A1.

Ideally I would like to delete all rows with cells in column A that have a length of 10 characters. Or alternatively, with a completely different set of code, delete all other rows that do not contain cells in column A that have a length of 10 characters.

Upvotes: 0

Views: 966

Answers (3)

user4039065
user4039065

Reputation:

Essentially, your loop is going through every cell in the Range.CurrentRegion property radiating out from A1. Your narrative expresses that you only want to examine column A but delete the Range.EntireRow property.

The For .. Next loop stepping backwards proposed by Scott Craner is likely your best bet but if you are more comfortable with a For ... Each In loop then yours can be adjusted.

Sub way()
    Dim cl As Range, dels As Range

    With Worksheets("Sheet1")
        For Each cl In .Range("A1").CurrentRegion.Columns(1).Cells
            If Len(cl.Value2) = 10 Then
                If Not dels Is Nothing Then
                    Set dels = Union(dels, cl)
                Else
                    Set dels = cl
                End If
            End If
        Next cl
    End With

    If Not dels Is Nothing Then _
        dels.EntireRow.Delete

End Sub

The logic to delete rows that did not have a value in column A that was 10 characters, symbols or digits long would be If Len(cl.Value2) <> 10 Then.

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152465

When deleting rows it is best to loop backwards:

Sub way()
Dim ws As Worksheet
Dim i As Long
Dim lastrow As Long


Set ws = ActiveSheet
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
For i = lastrow To 1 Step -1
    If Len(ws.Cells(i, 1)) < 2 Then ws.Rows(i).Delete
Next i

End Sub

Upvotes: 3

PKatona
PKatona

Reputation: 639

I haven't checked for syntax, but something like this should work:

dim idx as integer
idx = 1
while Range("A" + cstr(idx)).value <> ""
    'insert your delete logic here...
idx = idx + 1
wend

Upvotes: -2

Related Questions