Macro to delete all rows below Specific Text Starting from the first blank row

athermian

New Member
Joined
Oct 31, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,

I have these massive projects to convert 800 excel files with up to 200 worksheets into a DB.

As you can imagine the worksheet is divided into sections and each section is separated by a blank row.

Graham Mayor wrote this macro for me which I want to be modified to:

  • Search for a text string
  • When the string is found, look for the first empty row and then delete all rows below.
I would be grateful if you can help me modify this macro.



Thanks,

Ather

Sub DeleteBelow()
'Graham Mayor -- Last updated - 01 Nov 2020'
Dim lngFirstRow As Long, lngLastRow As Long
Dim lngCount As Long
Dim fRg As Range
Set fRg = Cells.Find(what:="Header Details", lookat:=xlWhole)
lngFirstRow = fRg.Row + 1
'If lngFirstRow < 11 Then lngFirstRow = 11 'to delete from row 11
lngLastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For lngCount = lngLastRow To lngFirstRow Step -1
Rows(lngCount).EntireRow.Delete
Next lngCount
Set fRg = Nothing
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

JB2020

Board Regular
Joined
Jul 29, 2020
Messages
75
Office Version
  1. 365
  2. 2016
  3. 2010
Platform
  1. Windows
I think this should work, assuming I have understood correctly

VBA Code:
Sub DeleteBelow()

    Dim KeepRange As Range
    Dim RangeAfterBlankRow As Range
    
    Set KeepRange = Cells.Find(what:="Header Details", lookat:=xlWhole).CurrentRegion
    Set RangeAfterBlankRow = Range(KeepRange.Resize(1, 1).Offset(KeepRange.Rows.count + 1), ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell))
    
    RangeAfterBlankRow.EntireRow.Delete
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,678
Members
415,921
Latest member
ExcelNoob28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top