Macro To Delete Rows

[Dazzawm]

Hi I need a macro that will delete certain rows. I need it to look at column 'L' and wherever there is a cell that has a letter 'D' as the last letter in that cell I want it to delete the entire row. thanks.

 

[bjurney]

Does it need to be a macro? You can just do this in a formula in another coulmn with the following:

=IF(RIGHT(A1,1)="D",TRUE,FALSE)

Then sort that coulmn for the TRUE's and delete the rows.

If it needs to be a macro, you can just record your self doing the listed steps, then post your code so we can clean it up for you.

 

[Dazzawm]

I thought a macro would be easier as I have a lot of data stretching across about 80 columns and 6000 rows, and I need it available to use on a lot of files.

 

[Dazzawm]

I have tried that formula, and they all return 'FALSE' anyway.

Its ok the formula was for column 'A' I changed it to column 'L' and it worked.

 

[cb12]

Loop this as necessary

Sub delete_rows()
Dim mystring, j As String
    mystring = ActiveCell.Value
    j = Right(mystring, 1)
        If j = "d" Then
            ActiveCell.EntireRow.Delete
        End If
End Sub

 

[bjurney]

This should do what you want it to do as well:

Sub deleterows()
 
    Range("M2:M" & Cells(Rows.Count, 1).End(xlUp).Row).FormulaR1C1 = "=IF(RIGHT(TRIM(RC[-1]),1)=""D"",TRUE,FALSE)"
    Range("M1").AutoFilter Field:=2, Criteria1:="TRUE"
    Range("M2:M" & Cells(Rows.Count, 12).End(xlUp).Row).Delete Shift:=xlUp
    Range("M1").AutoFilter
    Columns("M:M").Delete Shift:=xlToLeft

End Sub

 

[Peter_SSs]

Should be able to use AutoFilter without the use of any extra column (and without looping through each row).

Note that AutoFilter this is not case-sensitive though, so rows ending in "D" or "d" will be deleted.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("L1", Range("L" & Rows.Count).End(xlUp))<br>        .AutoFilter Field:=1, Criteria1:="*D"<br>        .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Dazzawm]

Thanks, but cb12 your macro did nothing and bjurney yours just messed up all my data (it was a copy).

 

[Dazzawm]

Thanks Peter yours worked a treat.