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]

It works, but it's still amazingly time consuming... Perhaps even more than going 1 by 1 (I haven't calculated to the second)...

And as jbeaucair mentioned, I started the range at RowCount +1.

Does anyone have a faster way to delete rows?

Thank you

Samfolds

 

[JackDanIce]

Hi Jb,

Is that line not meant to be:

Set delRange = Range("A" & LR + 10)

Rather than rw? Otherwise makes sense and liked what I learnt today!

Sams - I think the issue with this suggestion is that you're still looping through data. Did you try my original suggestion about setting some flag variable in an empty column to represent rows you want to delete, filtering for these rows only and then using specialcells(visible).delete (syntax wrong) - I think it'll be much quicker given the size of your data.

If size is say 3000, code would look something like:

Dim i as Long
i = Range("A" & Rows.count).end(xlup).row
' determine i as your last row

' assumes AA column is empty
With Range("AA2:AAA" & i)
  .formula = "=IF(A2="Keep",1,0)"
  .value = .value
End With
' this evaluates A2 (whatever your criteria is) and then applies same formula
' to all other rows, then hard codes the values in
Range("A1").CurrentRegion.Filter(can't remember the text but filter for 1s
' This selects all your data
xlSpecialcelltypes(visible).delete
' deletes those rows you flagged as wanting to delete (and hid the rows you
' wanted to keep)
' Some code to remove the filter

That would be much quicker than looping anyway...

 

[samfolds]

Ok, but if my condition is row-specific, then I'm doomed as I absolutely need to loop right?

Bleh

Thanks so much for your time I learned a lot

Samfolds

 

[JackDanIce]

What do you mean row specific, can you expand or give an example/explanation?

 

[samfolds]

Well I need to delete rows for 2 things :

1) Remove repeting data

2) If a specific variable is 40,50,60,70 then I also delete the row and I gotta check for each entry (row)

....

If you find a solution you're my hero :D

Samfolds

 

[jbeaucaire]

Sam, if you can explain how ONE row of data would be marked for deletion by checking the two things you want checked, I imagine a formula can be created that does the same check. The results of two-criteria check could be "OK" or "DELETE".

You place that formula into an empty column all the way down the data set all at once, FILTER that column by "DELETE" and then delete all rows left visible, all at once.

We just need to know what column represents "duplicate data" and which column has the values you want to check for deletion.

 

[samfolds]

I admit that setting that filter and deleting manually would probably be less CPU consuming, but I need to make that a macro so that it's easy for my collegues to run...

But basically, I need to delete any repeting row. There is an ID number in the first column (A) and any repeting ID must be deleted (the whole row).<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Secondly, in another column there is a code to know what type of pension plan the member is in. Depending on the type of pension plan, I need to delete the rows too. Possible codes are 10,20,30,40,50,60,70,80,P0,A0 and some others but the main part is, I got to delete de 50,60,70,80 ones.

Anyway to do that without looping 12 000 times (for my 12 000 rows) ?

Thanks in advance!

Samfolds

 

[jbeaucaire]

What column has the pension plan numbers...?

 

[samfolds]

Hi, JackDanIce found an interesting solution that I'm sharing for the benefit of all. For the moment it's not working on my dataset and I got to figure out why, but on a small sample it worked perfectly...

The frist part simply clears a certain type of data that I don't need. Then the row deleting processes...

Sub SamsMacro1()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'Stuff to initialise at start
'--------------------------------------------------------------------
Dim DataSheet As Worksheet
Dim DataSheetName As String
Set DataSheet = ActiveSheet
' If the data is on a specific worksheet (e.g. Sheet1 or different name)
' change line above to Set DataSheet = Sheets("<name of sheet>")
' including the " " but NOT the < or >
DataSheetName = ActiveSheet.Name
ActiveSheet.Select
' Code below deletes data for plans you don't want
'--------------------------------------------------------------------
Range("B:B").Replace what:="50", Replacement:="z50", lookat:=xlWhole
Range("B:B").Replace what:="60", Replacement:="z60", lookat:=xlWhole
Range("B:B").Replace what:="70", Replacement:="z70", lookat:=xlWhole
Range("B:B").Replace what:="80", Replacement:="z80", lookat:=xlWhole
'ActiveSheet.Sort.SortFields.Clear
' Do not delete the line below, it places a dummy z value for the find
' feature to work, otherwise if no z's found, it throws up an error
Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = "z"
Range("A:B").Sort key1:=Range("B:B"), Order1:=xlAscending, Header:=xlYes
Range("B:B").Find(what:="z", after:=Range("B1"), lookat:=xlPart, _
        MatchCase:=xlYes).EntireRow.Insert
Range("A1").End(xlDown).Offset(2, 0).CurrentRegion.ClearContents
'--------------------------------------------------------------------

' Code below removes duplicates
'--------------------------------------------------------------------
Sheets.Add.Name = "Temp"
DataSheet.Range("A:BO").AdvancedFilter xlFilterCopy, criteriarange:=Range("A1"), _
        CopytoRange:=Sheets("Temp").Range("A1"), unique:=xlYes
DataSheet.Delete
Sheets("Temp").Name = DataSheetName
'--------------------------------------------------------------------
End Sub

If you can figure out why this wouldn't work on a 11765 rows dataset from column A to BO, please let me know :D

Thank you!

Samfolds

 

[jbeaucaire]

So:

Column A has the ID numbers...we want to keep the first instance of each?
Column B had the pension plan number...certain one are to be deleted regardless of duplication.

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

Excel Workbook
	A
1	50
2	60
3	70
4	80
DeleteCodes

Then run the following macro...here's the before / after on a sample set of data:

Excel Workbook
	A	B
1	IDs	PP
2	1	10
3	2	20
4	1	30
5	1	40
6	2	50
7	3	60
8	4	70
9	1	80
10	12	90
11	2	100
12	1	110
13	13	120
14	4	130
15	5	140
16	6	150
17	7	160
18	8	170
19	9	180
20	1	10
21	2	20
22	1	30
23	1	40
24	2	50
25	3	60
26	4	70
27	1	80
28	12	90
29	2	100
30	1	110
31	13	120
32	4	130
33	5	140
34	6	150
35	7	160
36	8	170
37	9	180
Sheet7 - BEFORE

Excel Workbook
	A	B
1	IDs	PP
2	1	10
3	2	20
4	12	90
5	13	120
6	4	130
7	5	140
8	6	150
9	7	160
10	8	170
11	9	180
Sheet7 - AFTER

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
Application.ScreenUpdating = False

Set shData = Sheets("Sheet7")
Set shCodes = Sheets("DeleteCodes")

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,DeleteCodes!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.ScreenUpdating = True
End Sub