patternMinor
Deleting ranges of rows from a spreadsheet
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?
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 SubSolution
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
You can see @D_Zab's answer for an understanding on how to call subs or functions from other subs
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 SubYou 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 SubContext
StackExchange Code Review Q#77724, answer score: 4
Revisions (0)
No revisions yet.