HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Deleting ranges of rows from a spreadsheet

Submitted by: @import:stackexchange-codereview··
0
Viewed 0 times
rowsdeletingrangesspreadsheetfrom

Problem

I'm currently trying to create a macro, to edit a document that is around 280k rows and 12 columns. For the first steps I found a macro to delete the rows I don't want based on certain criteria, but the code repeats itself a lot. Every time I run it, I change the criteria and link the macros.

Also, is there a better way to reuse this code, rather than to modify it each time?

Sub jbeck1()
Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

If Range("E" & i) = "0020" Then
    Range("E" & i).EntireRow.Delete Shift:=xlUp
End If

Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.Run "jbeck2"

End Sub
Sub jbeck2()
Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = Cells(Rows.Count, 1).End(xlUp).Row

For i = LR To 2 Step -1

If Range("E" & i) = "0021" Then
    Range("E" & i).EntireRow.Delete Shift:=xlUp
End If

Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.Run "jbeck3"
End Sub

Solution

Instead of looping through every single line and then deleting it if it matches your criteria, you could build a string that contains the exact ranges you want to delete. Then once the loop is complete you could call a single Range().Delete call. You also can make the macro take in a string that you wish to delete.

Sub MacroDelete(S As String)

Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = Cells(Rows.Count, 1).End(xlUp).Row

Dim RangeToDelete As String
Dim Count As Integer
Dim numRowsToDelete As Integer

RangeToDelete = ""
Count = 0

' cheap way to get the total amount we want to delete
numRowsToDelete = Application.WorksheetFunction.CountIf(Range("E:E"), S) - Application.WorksheetFunction.CountIf(Range("E1:E2"), S)

For i = LR To 2 Step -1

' build the range string
If Range("E" & i) = S Then
    Count = Count + 1
    RangeToDelete = RangeToDelete & i & ":" & i
    If Count < numRowsToDelete Then
        RangeToDelete = RangeToDelete & ","
    End If
End If

Next i

' delete all the rows found matching S
Range(RangeToDelete).Delete Shift:=xlUp

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


You can see @D_Zab's answer for an understanding on how to call subs or functions from other subs

Code Snippets

Sub MacroDelete(S As String)

Dim i As Long
Dim LR As Long

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = Cells(Rows.Count, 1).End(xlUp).Row

Dim RangeToDelete As String
Dim Count As Integer
Dim numRowsToDelete As Integer

RangeToDelete = ""
Count = 0

' cheap way to get the total amount we want to delete
numRowsToDelete = Application.WorksheetFunction.CountIf(Range("E:E"), S) - Application.WorksheetFunction.CountIf(Range("E1:E2"), S)

For i = LR To 2 Step -1

' build the range string
If Range("E" & i) = S Then
    Count = Count + 1
    RangeToDelete = RangeToDelete & i & ":" & i
    If Count < numRowsToDelete Then
        RangeToDelete = RangeToDelete & ","
    End If
End If

Next i

' delete all the rows found matching S
Range(RangeToDelete).Delete Shift:=xlUp

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Context

StackExchange Code Review Q#77724, answer score: 4

Revisions (0)

No revisions yet.