Solved: Deleting Rows

[fpacheco]

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

[PAB]

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

[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]

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

[PAB]

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

[PAB]

Appologies for the two identical posts, I am not sure why that happened :dunno .

[fpacheco]

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!

[PAB]

Hi fpacheco,

Can you mark this thread as solved please.

Regards,
PAB