Delete row (if blank) below text string

Dave T · #1 01-09-2019, 07:51 PM

Hello All,

Probably quite simple but virtually all of the macros I have searched for show how to find a value or text string and delete the row the value is within.

I need to find the single instance of a cell that contains the words REVERSE DIRECTION (in uppercase and this is all the cell contains) and if the cell/row below the text is blank, delete the blank row, but if the cell below has text or a value do not delete the row (exit the sub).
For example: If the macro was to be re-run several times, I do not want the macro finding the same string and deleting existing data/rows.

In my case I have an extract output from another program and this is opened with Excel and the output has the following:
• the first 12 rows are headings and these headings are separated by blank rows

• the string REVERSE DIRECTION could be anywhere in column B

Regards,
David T

Kenneth Hobson · #2 01-09-2019, 09:09 PM

Code:

Sub rdRowM1()
  Dim r As Range, f As Range
  Set r = Range("B2", Cells(Rows.Count, "B").End(xlUp))
  Set f = r.Cells(r.Rows.Count, 1)
  Set f = r.Find("REVERSE DIRECTION", f, xlValues, xlWhole, xlNext)
  If f Is Nothing Then Exit Sub
  With f.Offset(1)
    If .Value = "" Then .EntireRow.Delete
  End With
End Sub

Dave T · #3 01-10-2019, 06:46 AM

Hello Kenneth,

Sorry about the late reply... school holidays and kids using the computer.

Your macro does exactly what I was after.
I really appreciate your reply.

Regards,
Dave T

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Why when I paste text into word, blank space appears after a paragraph & I can't delete it?	AWD	Word	3	10-16-2018 01:10 PM
Delete blank pages	Dimsok	Word VBA	18	11-14-2014 12:30 PM
Way to search for a string in text file, pull out everything until another string?	omahadivision	Excel Programming	12	11-23-2013 12:10 PM
Word delete page if string is not contained	wolfking333	Word VBA	0	06-23-2013 10:02 AM
Delete Blank Rows (Cyrillic Text in Spreadsheet )	dozd	Excel	1	02-22-2013 03:24 AM