Excel Macro to delete rows 1

[kabushnell]

I am creating a macro that I need to do delete rows if they begin with the text "f" or "e". I haven't been able to quite this to work right. Also with this same macro I would like to combine amounts in column d if entries in column A and column c are the same. I haven't been able to figure out this part either.

 

[SkipVought]

Hi,

Try using the AutoFilter to select row that meet your criteria. Turn on your macro recorder.

Combine amounts in column D if entries in column A and column c are the same. ??? Explain.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[kabushnell]

Thanks, I will try the AutoFilter with the macro recorder. For the combine what I have is Column A,B,C and D are like the following

a2727 1/21/2005 FKUSX 1053.210
a2727 1/21/2005 FKUSX 252.109
a2485 1/21/2005 ABNDX 10538.222
a2345 1/21/2005 MF4034 22140.453

What I would like to do is combine the amounts for a2727 FKUSX so there is only one row for that so it would look like the following:


a2727 1/21/2005 FKUSX 1305.319
a2485 1/21/2005 ABNDX 10538.222
a2345 1/21/2005 MF4034 22140.453

Sorry, it was kind of tough to explain without showing what I was asking.

Thanks for your help.

 

[SkipVought]

First you need column names for your columns of data.

You can do that with a PivotTable, using the SUM OF (the last column name)

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[fneily]

To delete rows beginning with F or E:

Sub DelRowContain()
Dim r As Long
Dim Lastrow As Long
Dim holdparcel As String
Application.ScreenUpdating = False
Lastrow = 50
For r = Lastrow To 2 Step -1
If Left(Cells(r, 1), 1) = "f" Or Left(Cells(r, 1), 1) = "e" Then
Rows(r).Delete
Else
End If
Next r
Application.ScreenUpdating = True
End Sub

 

[kabushnell]

I ran the code above and on my test files it deletes all but one row that begins with "f". Any thoughts as to why?

Also, anyone have any other ideas on how I can combine those amounts as shown above?

 

[fneily]

Could be two reasons: Does any row begin with a space then F or E? Do you have more then 50 rows?

Always understand the code someone gives you and then readjust it to fit your needs. Notice the code only looks at the first 50 rows and goes down to row 2 - row one is assumed to have headers.

 

[ltop]

Could you use the subtotal function to total the amounts at certain breaks on your list below:

a2727 1/21/2005 FKUSX 1053.210
a2727 1/21/2005 FKUSX 252.109
a2485 1/21/2005 ABNDX 10538.222
a2345 1/21/2005 MF4034 22140.453

and it would give something like below
a2747 Total 1305.319
a2485 Total 10538.222
a2345 Total 22140.453

regards

 

[KenWright]

As far as deleting rows goes, did you try using Skip's suggestion of autofiltering and then deleting. usually a lot quicker than looping through rows when you have large datasets, eg:-

Sub FiltDelete()

    Dim rng1   As Range
    Dim rng2   As Range
    Dim Lastrow As Long

    With ActiveSheet
        Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set rng1 = .Columns("A:A")
        Set rng2 = .Range(Cells(2, "A"), Cells(Lastrow, "A"))
    End With

    On Error Resume Next
    With rng1
        .AutoFilter Field:=1, Criteria1:="=f*", Operator:=xlOr, _
                    Criteria2:="=e*"
        rng2.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With

End Sub

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
---------------- Click here to help the tsunami victims ----------------