Deleting multiple rows at once in VBA

[samfolds]

Hi,

I need to make a macro that will delete a row when a condition is met. The problem is, I got more or less 2000 rows per worksheet to delete and it's very time consuming to delete them one by one.

So, is there a way to "store in a variable" all the rows I need to delete and then delete them all at once (like using HOLD Control + click rows and then delete) ?

Thanks!

Samfolds

 

[samfolds]

On a new sheet in the same workbook called "DeleteCodes", enter the codes to delete in column A.
DeleteCodes

I'd rather not as I'm trying to build an add-in to ease my collegues' lives...

Hopefully, they'll open a CSV file with excel and then press the little add-in button and voila... all the hard work will be done for them

Do you have a suggestion that would go this way? Otherwise, if you check Jack's method everything seems to work except that the filter does't want to extend pass the X column... (So I can get rid of my duplicates when my table goes from A to W but not further...) Is there a way around that?

Thanks.

Samfolds

 

[jbeaucaire]

Ok, this will create/delete the code sheet on its own.

Option Explicit

Sub DeleteDupesAndCodedItems()
' Jerry Beaucaire   (5/21/2010)
' First all items matching certain codes are deleted
' Then all remaining duplicates removed
Dim LR      As Long         'last row of data
Dim BR      As Long         'bottom row of visible data in autofilter
Dim shData  As Worksheet    'sheet with data to reduce
Dim shCodes As Worksheet    'sheet with delete codes to reference
Dim DelCode As Variant
Application.ScreenUpdating = False

DelCode = Array(50, 60, 70, 80)
Set shData = Sheets("Sheet7")
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
Set shCodes = Sheets("Temp")
shCodes.Range("A1").Resize(UBound(DelCode) + 1).Value = Application.WorksheetFunction.Transpose(DelCode)

With shData
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    
    .Range("E1:F1") = "key"
    .Range("E2:E" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC1,R1C1:R[-1]C1, 0))"
    .Range("F2:F" & LR).FormulaR1C1 = "=ISNUMBER(MATCH(RC2,Temp!C1,0))"

    .Rows("1:1").AutoFilter
    .Rows("1:1").AutoFilter Field:=6, Criteria1:="TRUE"
    BR = .Range("A" & .Rows.Count).End(xlUp).Row
    If BR > 1 Then .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible) _
                                            .EntireRow.Delete xlShiftUp
    .Rows("1:1").AutoFilter Field:=6
    .Rows("1:1").AutoFilter Field:=5, Criteria1:="TRUE"
    BR = .Range("A" & .Rows.Count).End(xlUp).Row
    If BR > 1 Then .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible) _
                                            .EntireRow.Delete xlShiftUp
    .AutoFilterMode = False
    .Range("E:F").ClearContents
End With

Application.DisplayAlerts = False
    shCodes.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Is the next question, "How do I make this an addin?"

 

[samfolds]

I might be a bit rusted but I believe it's a simple question of saving it as a .xla file in the excel.exe folder...

If I'm wrong feel free to enlight me

Thank you for the code Jerry!

Samfolds

 

[jbeaucaire]

You might want the macro available from a simple menu drop down. That usually takes some plumbing to automate. But you can Google up instructions on ways to do that, too. Glad to help with the macro, hope your users appreciate your efforts.