Deleting entire row based on blank cell in column

Question

We are using a SharePoint-exported Excel file for tracking Overtime. There are two aspects that influence this problem I am having:

The employee shows they worked by entering Start Time in Column 'P'
If overtime was planned but the employee did NOT work the OT, they leave the Cell in Column 'P' BLANK.

This could even be a setup error. I added a Module to the PERSONAL.xlsb VBAProject.

Then, I tried a bunch of code I found on here searching for similar cases but nothing seems to help.

I am using Excel 2016.

I tried numerous code but this seems the simplest:

Sub NewBlanks()
    Dim LastRow As Integer
    LastRow = Range("A" & Rows.Count).End(xlUp).row
    Range("P2:P" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

I just want the macro to look at column 'P' and, if a cell is BLANK, delete the entire row, moving rows up to fill that gap.

`Dim LastRow as Long` instead of `As Integer`. Are you running into any errors? — BigBen, Jul 09 '19 at 14:45
Use Autofilter as shown here [delete row based on condition](https://stackoverflow.com/questions/11317172/delete-row-based-on-condition) — Siddharth Rout, Jul 09 '19 at 14:47
@BigBen I am getting an error: Delete method of Range class failed — Jeremy, Jul 09 '19 at 17:28
@SiddharthRout Using the code from the linked page... How do i modify that to look for blanks in Column P instead of the 'FemImplant$ term? Thank you. — Jeremy, Jul 09 '19 at 17:35

score 0 · Answer 1 · answered Jul 09 '19 at 15:12

0

Give this a try :

Sub DeleteRows()
Dim mFind As Range

Set mFind = Columns("P").Find("")
If mFind Is Nothing Then
    MsgBox "There is no cell found with the text ''" _
    & " in column P of the active sheet."
    Exit Sub
End If

firstaddress = mFind.Address

Do

    Range(mFind, Cells(mFind.Row, "P")).EntireRow.Delete
    Set mFind = Columns("P").Find("")
    If mFind Is Nothing Then Exit Sub
    Set mFind = Columns("P").FindNext(mFind)

Loop While mFind.Address <> firstaddress

End Sub

Kindest regard

answered Jul 09 '19 at 15:12

TourEiffel

4,034
2
16
45

Thank you. This code gave me a 'Compile Error: Variable not defined' It highlighted: firstaddress = – Jeremy Jul 09 '19 at 15:26
@jeremy then just declare it at the top of the sub – TourEiffel Jul 09 '19 at 15:33
I do not know how to do that. – Jeremy Jul 09 '19 at 17:44
`Dim firstaddress As String` – BigBen Jul 09 '19 at 18:35

Deleting entire row based on blank cell in column

1 Answers1