View Full Version : Solved: Deleting Rows
fpacheco
08-31-2012, 03:15 AM
Hello all,
I want to delete rows two by two within certain range and independently of the content. Example: row7, row8, row10, row11, row13, row14....
Have you a clever hint?
Regards
Hi fpacheco,
What about something like this?
Sub Delete_SPECIFIC_Rows_1()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Range("7:8,10:11,13:14").EntireRow.Delete
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
I hope this helps!
Regards,
PAB
Hi fpacheco,
If there are many rows to delete then you could use this:
Sub Delete_SPECIFIC_Rows_2()
Dim iLastRow As Long
Dim i As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
For i = iLastRow To 1 Step -3
Rows(i).Resize(2).Delete
Next i
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
But if the last ROW is NOT one that you want deleted then replace the lines:
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
with this:
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If iLastRow + 1 Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow + 1 Mod 3 <> 1 Then iLastRow = iLastRow - 1
I hope this helps!
Regards,
PAB
Teeroy
08-31-2012, 04:24 AM
How about something like this?
Sub delete_rows()
Dim withinrng As Range
Dim delrng As Range
Dim rng As Range
Set withinrng = Range("A7:A25")
Set rng = Range("a4")
Set delrng = Nothing
Do
If Not Intersect(rng, withinrng) Is Nothing Then
Set rng = rng.Offset(3, 0)
If delrng Is Nothing Then
Set delrng = Range(rng.Offset(-3, 0), rng.Offset(-2, 0)).EntireRow
Else
Set delrng = Union(delrng, Range(rng.Offset(-3, 0), rng.Offset(-2, 0)).EntireRow)
End If
Else
Set rng = rng.Offset(1, 0)
End If
Loop While rng.Row < withinrng.Cells(withinrng.Rows.Count).Row
delrng.Delete
End Sub
Hi fpacheco,
If there are many rows to delete then you could use this:
Sub Delete_Specific_Rows()
Dim iLastRow As Long
Dim i As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
For i = iLastRow To 1 Step -3
Rows(i).Resize(2).Delete
Next i
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
But if the last ROW is NOT one that you want deleted then replace the lines:
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
with this:
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
If iLastRow + 1 Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow + 1 Mod 3 <> 1 Then iLastRow = iLastRow - 1
I hope this helps.
regards,
PAB
Appologies for the two identical posts, I am not sure why that happened :dunno .
fpacheco
08-31-2012, 09:39 AM
PAB: it worked obviously better the second code as it was a huge range
Teeroy: your code line worked as well
Thank you both for your quick and good answer. It's amazing how can a simple task (delete a row) have such a complicated algorithm (my opinion), and how could you bring it to me nice and easy. Cheers!
Hi fpacheco,
Can you mark this thread as solved please.
Regards,
PAB
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.