Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-09-2019, 07:51 PM
Dave T Dave T is offline Windows 7 64bit Office 2013
Advanced Beginner
 
Join Date: Nov 2014
Location: Australia
Posts: 32
Dave T is on a distinguished road
Default Delete row (if blank) below text string

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
Reply With Quote
  #2  
Old 01-09-2019, 09:09 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Novice
 
Join Date: Jun 2018
Posts: 27
Kenneth Hobson is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 01-10-2019, 06:46 AM
Dave T Dave T is offline Windows 7 64bit Office 2013
Advanced Beginner
 
Join Date: Nov 2014
Location: Australia
Posts: 32
Dave T is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 01:56 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft