VBA- Delete Rows Above and Below Based on Criteria

[aeroguy1]

Hello,

I am trying to find a code to delete rows based on criteria in column A. So based on a cell in column A, the code will delete one row above, the row in question, and 25 rows below the row of a cell in column A that matches criteria "none". These rows change numbers based on other rows that are deleted above them in another code.

So basically: If cell in column A contains "none", delete that row, the row above, and 25 rows below.

Thank you!

 

[dave3009]

Hi

Hope this might help you along the way.

Sub DeleteRows()
Dim last As Long
Dim i As Long
With ActiveSheet
    last = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = last To 3 Step -1 '3 assumes you have a header row
    If .Cells(i, 1).Value = "none" Then
        .Cells(i - 1, 1).Resize(27, 1).EntireRow.Delete
    End If
Next i
End With

End Sub

Hope it helps

Dave

 

[aeroguy1]

Thank you so much! Works perfectly!

 

[akahrim]

Hi...got private message saying you responded to my request, but I cannot access it.
Please email here or to zakahrim@gmail.com.
Thanks
AK

 

[akahrim]

Hi....Please edit macro to do following :

I would like to delete all records older than or equal to year 2010 from a customer database.
Each customer record consists of 5 rows, the date is in Col.D, 2nd row. of 5 rows.
Date cell format is (e.g.) 08/14/2004
How do I modify your macro to delete - the date row, 1 row above, and 3 rows below?

Thanks
AK

 

[silentwolf]

Sorry,

made a mistake with answering that question.


I got the same issue but unfortunatelly I can not get it to work.

I like to find in Column A if the word "*Saldenliste" is found delete including the found row and 7 Rows Above and 6 Rows below that entry all rows.

there is only one instance of that word in that column I do habe a header but that would not affect the "String I am searching for"

Sub DeleteRows()
Dim last As Long
Dim i As Long
With ActiveSheet
    last = .Cells(.rows.Count, 1).End(xlUp).Row
For i = last To 1 Step -1 'Changed that from the original code!!!
    If .Cells(i, 1).Value Like "*Saldenliste" Then
        .Cells(i - 7, 1).Resize(6, 1).EntireRow.Delete
    End If
Next i
End With

Can someone please help me with this..

Many Thanks

 

[silentwolf]

Sorry,

made a mistake with answering that question.


I got the same issue but unfortunatelly I can not get it to work.

I like to find in Column A if the word "*Saldenliste" is found delete including the found row and 7 Rows Above and 6 Rows below that entry all rows.

there is only one instance of that word in that column I do habe a header but that would not affect the "String I am searching for"

Sub DeleteRows()
Dim last As Long
Dim i As Long
With ActiveSheet
    last = .Cells(.rows.Count, 1).End(xlUp).Row
For i = last To 1 Step -1 'Changed that from the original code!!!
    If .Cells(i, 1).Value Like "*Saldenliste" Then
        .Cells(i - 7, 1).Resize(6, 1).EntireRow.Delete
    End If
Next i
End With

Can someone please help me with this..

Many Thanks

 

[silentwolf]

Sorry,

got it working!

The Resize had to be changed!

All good

So no need to help ggg