Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-09-2019, 07:51 PM
Dave T Dave T is offline Delete row (if blank) below text string Windows 7 64bit Delete row (if blank) below text string Office 2013
Advanced Beginner
Delete row (if blank) below text string
 
Join Date: Nov 2014
Location: Australia
Posts: 66
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 Delete row (if blank) below text string Windows 10 Delete row (if blank) below text string Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
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 Delete row (if blank) below text string Windows 7 64bit Delete row (if blank) below text string Office 2013
Advanced Beginner
Delete row (if blank) below text string
 
Join Date: Nov 2014
Location: Australia
Posts: 66
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
Delete row (if blank) below text string 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 row (if blank) below text string Delete blank pages Dimsok Word VBA 18 11-14-2014 12:30 PM
Delete row (if blank) below text string 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 row (if blank) below text string Delete Blank Rows (Cyrillic Text in Spreadsheet ) dozd Excel 1 02-22-2013 03:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:39 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft