Excel VBA - Delete Rows Based on Criteria

Question

I have a report that I pull everyday that is placed in a very awekward format. It's contains a variable row count by 4 columns organized into unofficial tables based on the Name of each employee.

What I have is an employee name in column B preceded 2 blank rows above and followed by 1 blank row of data below.

What I want to accomplish is loop through the data, identify cells in column B <> blank, delete the entire 2 rows below that cell, and delete the entire 1 row above that cell.

Below is what I have so far. not much:

Sub test()

Dim currentSht As Worksheet
Dim startCell As Range
Dim lastRow As Long
Dim lastCol As Long
Dim i as integer

Set currentSht = ActiveWorkbook.Sheets(1)

Set startCell = currentSht.Range("A1")
lastRow = startCell.SpecialCells(xlCellTypeLastCell).Row
lastCol = startCell.SpecialCells(xlCellTypeLastCell).Column

For i = lastRow To 1
    If Cells(i, "B").Value <> "" Then


End Sub

David Zemens · Accepted Answer · 2017-01-12 17:00:49Z

1

without making major changes to your code, try this:

For i = lastRow To 1 Step - 1
    If Cells(i, "B").Value <> "" Then
    Range(Cells(i, "B").Offset(1), Cells(i, "B").Offset(2)).EntireRow.Delete  'delete two below
    Cells(i, "B").Offset(-1).EntireRow.Delete ' delete one above

You already get to your non-blank cell (ie Cells(i,"b")). To reference a range in relation to a cell you already have, use OFFSET.

So, and in this order, you select a range of cells from one below your cell Offset(1) to two cells below Offset(2)'. Change this range toENTIREROW` for those cells, and delete.

Then you select the cell above Offset(-1), select the ENTIREROW and delete.

edited Jan 12, 2017 at 17:00

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

answered Jun 6, 2016 at 23:20

EoinS

5,4921 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Andy Over a year ago

I am a little confused by this line of code: Range(Cells(4, 1).Offset(1), Cells(4, 1).Offset(2)).EntireRow.Delete. Shouldn't my selected range (to be offset) be in reference to cells(i,"B")? I don't know why you are using (4, 1).

EoinS Over a year ago

@AndyG was using (4,1) in a quick sample i set up locally, changed to (i, "B"), thanks!

Scott Craner Over a year ago

You are missing the Step - 1 in the for loop.

EoinS Over a year ago

@ScottCraner can you elaborate? I removed End Sub, as there is an End If and Next that I omitted just to show the row.delete portion, would it be better to add those for completeness?

Scott Craner Over a year ago

If you are looping from a larger number to a smaller you need to tell it to step -1. The default is step +1. It will do the loop once then end.

|

user3598756 · Accepted Answer · 2016-06-07 07:27:02Z

0

as per your question narrative you'd possibly need to delete all rows that has a blank cell in column "B"

should that be the issue than you could (disclaimer: test it on a copy sheet!) simply go like follows:

Sub test()    
    With ActiveWorkbook.Sheets(1)
        .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

answered Jun 7, 2016 at 7:27

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Collectives™ on Stack Overflow

Excel VBA - Delete Rows Based on Criteria

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related