VBA Question - Find next empty cell in column E and then select entire row

[Look4Jay]

Hi Guys,

Could you please help me to write a VBA code?

I want to find a next empty cell in column E, then select the entire row and eventually select everything below and delete it. Below is an example for your reference.
A B
1 Account Code Account Name
2 1111 Bank Name
3 1112 Bank Name
4 1113 Bank Name
5 1114 Bank Name
6 1115
7 1116
8 1117
9 1118

So eventually I want the VBA code can delete everything from Row 6, based on the next empty cell under column B.

Currently I can only find one VBA code but that only helps me to select the next empty row in column E.

Range("E" & Rows.Count).End(xlUp).Offset(1).Select

Thanks a lot guys.

 

[Trebor76]

Hi Look4Jay,

Welcome to MrExcel!!

Try this (firstly on a copy of your data as the results cannot be undone if they're not as expected):

Option Explicit
Sub Macro1()

    Dim lngLastRow As Long
    
    lngLastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    Range("B1:B" & lngLastRow).SpecialCells(xlBlanks).EntireRow.Delete

End Sub

Regards,

Robert

 

[Look4Jay]

Awesome, thanks a lot Robert, it works like a charm.

Happy new year

Regards
Jay

 

[Trebor76]

Awesome, thanks a lot Robert, it works like a charm.

Happy new year

Regards
Jay

You're welcome and you too