Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Macro Find Word & Delete All Rows Below

I have a 3rd party system report that I am writing a macro to convert quickly into a useful table of data. Naturally the number of rows in the report & number of useful rows varies each month. I want my macro to find the word "reinsurer" and delete the row it is on AND all rows beneath that row which contain text.

Similarly on the same base data but on a different sheet I need to find the word "reinsurer" and delete that row and all rows above it leaving just the data beneath to work with.


Post your answer or comment

comments powered by Disqus
I have a report that comes in a txt file. After importing into excel i am left with a bunch of garbage that i dont need. This report is anywhere from 5-15 pages depending on how much product was made that shift. I only need the information off of the 1st page.

My question is how can i Find the first occurance of specific text (Site) (will be in column A) and have it select that row and all rows below and delete them.

My biggest issue is the first page can be anywhere from 40-60 rows so I need to find the text (Site) and delete everything below it, which could be anywhere from 500-1500 rows.

Any suggestions?

I have searched high and low for a more efficient macro than the current loop i have.

I have a range that varies in size but always follows the same format. After i reach a certain value output from a formula, "4", I want to delete all lines below that line. So I want to find value "4" then move one row down and delete all lines to line 2004.

I have attached the macro that i compiled but it takes a couple of minutes to run.


	VB:
	
 manual() 
    Dim Firstrow As Long 
    Dim Lastrow As Long 
    Dim Lrow As Long 
    Dim CalcMode As Long 
    Dim ViewMode As Long 
     
    With Application 
        CalcMode = .Calculation 
        .Calculation = xlCalculationManual 
        .ScreenUpdating = False 
    End With 
     
    With ActiveSheet 
         
        .Select 
         
         
        ViewMode = ActiveWindow.View 
        ActiveWindow.View = xlNormalView 
         
        .DisplayPageBreaks = False 
         
         'Set the first and last row to loop through
        Firstrow = 4 
        Lastrow = 2004 
         
         'We loop from Lastrow to Firstrow (bottom to top)
        For Lrow = Lastrow To Firstrow Step -1 
             
             'We check the values in the A column in this example
            With .Cells(Lrow, "A") 
                 
                If Not IsError(.Value) Then 
                     
                    If .Value = "DELETE LINE" Then .EntireRow.Delete 
                     'This will delete each row with the Value ""
                     'in Column A, case sensitive.
                     
                End If 
                 
            End With 
             
        Next Lrow 
         
         
    End With 
     
    ActiveWindow.View = ViewMode 
    With Application 
        .Cursor = xlDefault 
        .StatusBar = False 
        .ScreenUpdating = True 
        .Calculation = CalcMode 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have a macro that uses the code listed below to delete formulas from all rows below any cell in column A that started with zero. I need to modifiy this to use in another workbook to delete all rows once the word "acct#" is encountered in column A. I tried replacing the = 0 with = "ACCT#" but it didn't work and my VBA skills are pretty abysmal. Any help would be greatly appreciated.


	VB:
	
 
Dim LastRow As Long, r As Long 
LastRow = Range("A65536").End(xlUp).Row 
For r = 1 To LastRow 
    If Cells(r, 1) = 0 Then 
        Range("A" & r & ":A" & LastRow).EntireRow.Delete 
        Goto Done 
    End If 
Next r 
Done: 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I would like to be able to find the cell in column A with the name of "Grand", and take that row and all the rows below that and delete the contents... Since everday though the number of rows will be different I can't just record this... here is an example spreadsheet...

Hi All,

Looking for some VBA code that will look for the first instance of a certain value in Column A, (Begins with 9128),then delete all rows above that row. Then I need to look for a value in Column A that begins with 9129, and delete all rows below that. I've attached a sample workbook to illustrate.

Thanks for any help.

JL

Hello.

I am trying to write a macro which will delete all rows in a worksheet except the header. I have a workbook with 30 sheets and I wanted to have it call each worksheet name and then do the same for each so that I can clear out old data for the new weeks report.

Thanks in advance for your help!

Sorry guys being a bit lazy as just finishing work so one fo the weekend to ponder.

What I am trying to add into my VBA is to select & delete all rows with the sole exception of Row1.

Reason is the 1st has headers and a button for users to click to run another macro. I could delete all rows but that gets rid of my button and the users won't be able to remember a keyboard shortcut.

Can anyone help.

Oh it's for the 97 version.

Thks
Jon

Hello!
I have recorded a macro that will delete all rows if data in Column "A1" is blank. However, I want the macro to identify which rows to delete by Column Name instead of "A1". If the Column name doesn't exist than the code should do nothing.

This is my current code:

	VB:
	
 GetX() 
     
    Application.ScreenUpdating = True 
    [a:a].AutoFilter Field:=1, Criteria1:="=" 
    [a2:a65536].SpecialCells(xlVisible).EntireRow.Delete 
    If [a1] = "" Then [1:1].Delete 
    ActiveSheet.AutoFilterMode = False 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

This is an example of what I want it to do:

HMO/POS(A1) Number (A2) OWNER Color AAA 11 ABC, FF RED CCC 37 RED 45 ASD YELLOW 23 DDD 34 ZZZ BLUE FFF 23 YELLOW 67 GGG 43 GGG RED Output:

HMO/POS (A1) Number (A2) OWNER Color AAA 11 ABC, FF RED CCC 37 RED DDD 34 ZZZ BLUE FFF 23 YELLOW GGG 43 GGG RED

Although the current code works, it takes "A1" instead of the column name "HMO/POS". Any help on this would be appreciated.

I am trying to create a macro that will delete everything below a certain word.

The code I tried to use is the following. This produced an error:

	VB:
	
 
    iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" NET INCOME").Row + 1 
Else 
    iStart = Range(Cells(1, 1), Cells(180, 1)).Find(" Interest Expense").Row + 1 
End If 
Rows(iStart & ":" & FinalRow).Delete shift:=xlUp 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thank you!

Hiya,

I have the following code, to find the 1st cell in column CG that contains a value of more than -200. I wish to delete the entire row that this resides on, and all rows below. Please can anyone help? Thanks!

Code:
Range("CG2").Select
    Do While Not Selection.Value > -200
        Selection.Offset(1, 0).Select
    Loop


Hi,

I have a range of values in Column A (A4:A500) which have hundreds of detailed product descriptions, these descriptions vary but will always contain a keyword. I want to narrow this list down to one or two key products. Is there a way I can delete all rows which don't contain certain keywords. To keep things simple, lets say I wanted to keep all descriptions which contain "apple" or "pear".

Anyone know how I might do this so I can make it in to a macro. I've tried to use advanced filter but as descriptions vary I can't can't make it filter results which just contain specific words. Help would be much appreicated.

I want to delete all rows that contain "M" in the below query. The auto filter is filtering rows containing m in column A. how can I delete these rows where ever a m appears in the column?

This is my macro below. I do not know what to put in the middle of the query

Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="m"

Selection.Delete Shift:=xlUp

thanks

This seems simple, but I'm having trouble with it... I've also looked at other posts but I haven't found anything that matches my issue. I've seen alot of questions regarding deleting rows/cells that meet certain criteria, but I'm looking for a macro that finds a particular cell (in my case, a cell labeled "View Data") and then deletes all rows above that cell. Unfortunately, the "View Data" cell is sometimes found in Row 60, sometimes in Row 65, Row 58, and so on. In each case, once that cell is located I'm having to manually delete all rows above that cell, so oviously I'm looking for some way to automate this.

Can anyone steer me in the right direction... thanks in advance!

Last processed row is rr.
What's the code to delete all the rows below that. Please help? : D

Hi

I have a sheet that has the word "list" in column A near the bottom of the sheet

I want to be able to delete all rows below and including the row with "list" in it

Thanks for any help

Mark

Does anyone know how I could write a macro to delete all rows from the first
empty row to row 2000 please? If I use the control+shift+down arrow and then
go down one cell, the macro programmes the precise cell reference -- but this
changes on datasheets with different amounts of data. HELP PLEASE!!!

Hello I need a macro to delete all rows except row 1 thanks

how do i find and delete all empty rows in an excel worksheet that was
created by exporting a double spaced crystal report ?

I need a macro to delete all rows that do not contain a certain value. The value that needs to be looked at will always be in column A. For example:

ColA: 0541073600000040320000, ColB: 95.4' X 401.3' IRR LOT 29 GREEN OAKS
ColA: 0541093000000130020000, ColB: 50'(S) X 20'(S) IRR COM AT NE COR OF

So what will need to be looked at every time is the first two digits in ColA. I need a popup box to ask me for the value because it can be a number of different values. But it will always be the same two digits for the particular spread sheet.

Ok so for example I will implement a button on any spread sheet I create and when I click it I need it to ask me what two digits need to be looked for. In this case I would put 05 and then it would go through and delete every row where ColA does not start with 05.

On a different spread sheet the digits may be 02 for example. If anyone could help me with this it would be very much appreciated.

Also I would like to have the macro just make one major delete instead of a bunch of individual deletes. If you need to see the spread sheet please let me know and I will zip it up and give it to you.

Thank you.

Hello Everyone,
I'm hoping this is easy for someone to understand.. it's a little above me at this point. I wanted to select some criteria based on an AUTOFILTER and then have a macro delete all rows from 2 to the bottom... then SHOWALLDATA pick another set using AUTOFILTER and then having excel repeat and delete everything 2 down... the problem i'm finding of course is that when you AUTOFILTER row number 2 is not always at the top, so I need some relative I think..

sample code:

Sub Macro3()

    Range("G3").Select
    Selection.AutoFilter Field:=7, Criteria1:="Initial cycle year"
    Rows("3:400").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
    Range("G3").Select
    Selection.AutoFilter Field:=7, Criteria1:="Product Activation"
    Rows("5:5").Select
    ActiveWindow.SmallScroll Down:=43
    Rows("5:200").Select
    ActiveWindow.SmallScroll Down:=209
    Rows("5:400").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData

End Sub


Does anyone know how I could write a macro to delete all rows from the first
empty row to row 2000 please? If I use the control+shift+down arrow and then
go down one cell, the macro programmes the precise cell reference -- but this
changes on datasheets with different amounts of data. HELP PLEASE!!!

Greetings everyone!

How can I delete all rows in an excel sheet except the first row?

I have an excel sheet where the first row has header information and the subsequent rows contains data. I would like to run a vba macro that clears/delete all the data , starting from row 2, in the excel sheet but retains the header information in row 1.

Curtney Jacobs

How can i do this? Actually what i want to do is delete all rows that contain one of like 5 diffrent things.. if ANYWHERE in the row it has that word, i want the entire row DELETED, not just blanked out so i am left with a blank row..

Can somebody help me through this and walk me through where to enter the code?

-realized

I have some VBA code that deletes a few rows at the conclusion of the macro. As a result, after the macro is run, a box pops up that says "Delete All Rows", and I have to manually click this.

Someone else running the macro might not know what this will delete, whereas I do. "Delete All Rows" sounds dangerous, right .

So, can I add a line to automatically select yes to this box so that the user never sees it pop up in the first place?

Thanks!


No luck finding an answer? You could always try Google.