Free Microsoft Excel 2013 Quick Reference

VBA method for deleting row + 1 row above

Hello all,

I am trying to create a macro that will help me clear certain rows from a large spreadsheet.

I wish to delete rows with the text "FULL REFUND" in column K. I have managed to create the following macro to enable me to do this.

Sub DeleteRefund()
'
' DeleteRefund Macro
'
Firstrow = 2
Lastrow = 10000
Range("k1:k10000").Select
Do
If ActiveCell = "FULL REFUND" Then
ActiveCell.EntireRow.Delete shift:=xlUp
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(1, 0)) And IsEmpty(ActiveCell.Offset(2, 0)) And IsEmpty(ActiveCell.Offset(3, 0)) And IsEmpty(ActiveCell.Offset(4, 0)) And IsEmpty(ActiveCell.Offset(5, 0)) And IsEmpty(ActiveCell.Offset(6, 0)) And IsEmpty(ActiveCell.Offset(7, 0)) And IsEmpty(ActiveCell.Offset(8, 0)) And IsEmpty(ActiveCell.Offset(9, 0)) And IsEmpty(ActiveCell.Offset(10, 0))
' The above line runs the hotel removal macro until it finds at least 10 rows in column k empty
'
End Sub
Evidently this isn't a very clean macro and it doesn't also delete the row above "FULL REFUND".

My questions to anyone who can help are:

1) Can you give me an example of how to delete the "FULL REFUND" rows and 1 row above
2) A line of code to end the macro when it finds empty rows at the bottom of the sheet.

Many thanks in advance.

Regards,
Jake


Post your answer or comment

comments powered by Disqus
Hello

I have referred to a previous discussion called "VBA Code to delete rows which contain a certain character" but whilst this has helped, I could do with a bit more guidance if possible.

A sample of the data I'm working with looks like this (in column C):

Microsoft Office XP Standard
Security Update for Windows XP (KB927802)
Windows XP Hotfix - KB918439
Windows Installer 3.1 (KB893803)
Shockwave
Squirrel SQL v2.1
Update for Windows XP (KB911280)
Screensaver
etc...

I would like to find out the best way of deleting all the rows that contain the following text:
"hotfix"
"security update"
"windows installer"
"update for windows XP"

I was thinking along the lines of the suggestion offered in the "VBA Code to delete rows which contain a certain character" - to use autofilter but even using macros, I am struggling to turn this into code.

Any help would be much appreciated.
Thanks
Grace

I have a speadsheet full of data and in row N some of the cells are formatted red.

I want a formula that says when N1 (for example) is white then delete row 1.

I have 60,000 row to sort through so something quick would be great!

USD $10 by paypal vba code to delete rows in workbooks

Hi again brilliant forum

i am an excel novice

had some great work done by Wigi and Krishnu in the past

I have about 120 excel files (each has about 1 million rows and between 6 and 30 Columns).

the files are 200mb + so too big to upload

i want a vba macro to delete unwanted rows

is it possible to have a message or input box that i can enter up to 5 words and if any of these words are contained in any rows then i wish to keep these rows only (and if any of these words dont appear i wish to delete the whole row)

the macro will be deleting about 950 thousand rows and keeping about 50 thousand (hopefully).

i can do it very slowly with custom sort but if a macro can be done it will make life easier

Good luck if you fancy the challenge

Paul

Macro for deleting rows and serialising the remaing rows
================================================== ==

I have a spread sheet

It's a ToDo List

The structure is like this

Column A Sl No

Column B Task

Column C Person

Column D Completed

To start with I keep entering the tasks in one stretch and take a print out.

The first Column (A) will be a serialized.

I wil be using this print out for few days.

After two/three days, I would like to open the spread sheet and enter fresh
tasks

For all completed tasks I put x in Col D.

What I currently require is a macro to

1.. Remove all rows where Column D contains 'x" (quotes not included.)
2.. After this operation, contents of Column A will have to be serialsed.

seena

---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 081111-0, 11/11/2008
Tested on: 11/12/2008 6:00:16 PM
avast! - copyright (c) 1988-2008 ALWIL Software.
http://www.avast.com

I'm trying to get a VBA macro put together that would delete all the rows, throughout every sheet, and shift up wherever the cell in column A = "Delete Row" in a workbook I've made. In the below copy of the workbook you can see that a lot of the information from all the tables is linked to the topmost table on the 'Mix Detail Sheet'.

If the the Cell in column A is blank on that first table, its corresponding cells in tables throughout the workbook displays the words "Delete Row". I need the macro to delete the rows and shift everything up where this occurs.

Ideally I'd also like the macro to then delete the rows in that first table where the column A cells are blank.

Let me know if I need to be more clear of if there are any questions. And thank you to anyone who can find the time to help me out. I've tried to adapt many of the other 'Delete Row' macros on this site to my situation, but I have not had very much success and I thought it was time to ask.

I have an Excel (Version 2002) table (Table 1) with 5 columns and 101 rows (1 row for title and 100 rows for records)
 Column 1: Product Description
 Column 2: Manufacturer
 Column 3: Sales for 2000
 Column 4: Sales for 2001
 Column 5: Sales for 2002

I would like to:

3) Delete all rows that have no data in in any of columns 3, 4, 5 (if there is data in one of the columns, then row must not be deleted). This will give me Table 2
4) I next want to rearrange Table 2 as follows: 4 columns and 301 rows ( 1 row for title and 300 rows for records):
a. Column 1: Product Description
b. Column 2: Manufacturer
c. Column 3: Sales
d. Column 4: Year

(If I use a cut and paste method, I would copy rows 2 to 101 and paste it twice, change the title from "Sales for 2001" to “Sales”, move the figures from "Sales for 2002" and "Sales for 2003" into the appropriate cells of the "Sales" column, delete the now empty columns that contained sales for 2002 and 2003, create a new column for year and add the year date in the corresponding cells for the sales figures).

Would greatly appreciate help in using VBA programming to do this.

Thank you

Hello All:

I have the following spreadsheet situation:

K---------L--------M
02/08---03/08----04/08
01/08------------------
03/08---04/08----05/08
02/08---04/08----05/08
04/08------------------

I would like help in VBA code too delete all rows with Entries in cells in Column L.

For instance in the sample above I'd be left with two rows.
01/08
04/08

Any help would be appreciated.

Hi

I found a macro deleting rows on a certain condition on this board...it works great.

Just two questions.

1. I would like to macro to only delete an entire row if all values in that row are equal to "0.0"?

2. Since I'm a novice with VBA I don't understand what the icount does and would appreciate it if someone could briefly explain it to me?

------------------------------
Sub DELETEZEROS()

Dim icount As Integer
Dim strtext As String
Dim rfound As Range

Application.Calculation = xlCalculationAutomatic
Columns("b:n").Select
Set rfound = Range("b10:n500")
For icount = 1 To WorksheetFunction.CountIf(Columns(13), strtext)
Set rfound = Selection.Find(What:="0.0", After:=ActiveCell, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, _
searchdirection:=xlNext, MatchCase:=False)
On Error GoTo done:
rfound.EntireRow.Delete
Next icount
done:
End Sub
--------------------------------
Many thanks

Sanet

What VBA code will delete rows in Worksheet 1 if the value of 6 appears in column W going from the last non-blank cell up to row 2?

Many thanks for your help,
Tom

Dear Sir,

I need an VBA command to delete the rows of containing the values of PK & Blank, from the column H.

Kinldy help me in this, I have attched the raw data for your reference.

Regards,
Mohan

Hi,

I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.


	VB:
	
Workbooks.Open Filename:="[COLOR="Yellow"][COLOR="Red"]C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS -
DON''T DELETE.xls]Sheet1[/COLOR][/COLOR]" 
 
 
UserGRP_MAcro Macro 
 ' Macro recorded 2009/05/19 by msimantb
 '
 
 '
Rows("1:3").Select 
Selection.Delete Shift:=xlUp 
Columns("A:B").Select 
Selection.Delete Shift:=xlToLeft 
Columns("B:E").Select 
Selection.Delete Shift:=xlToLeft 
Columns("A:A").EntireColumn.AutoFit 
Rows("2:2").Select 
Selection.Delete Shift:=xlUp 
Range("B1").Select 
ActiveCell.FormulaR1C1 = "Existing userGroup" 
Range("B2").Select 
Columns("B:B").EntireColumn.AutoFit 
ActiveCell.FormulaR1C1 = _ 
"=OFFSET('[INFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1'!R1C1,1,0)" 
Range("B2").Select 
ActiveCell.FormulaR1C1 = _ 
"=OFFSET('[INFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1'!R[-1]C1,1,0)" 
Range("B2").Select 
Selection.AutoFill Destination:=Range("B2:B59") 
Range("B2:B59").Select 

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


Good day to all, I"m looking for a VBA code to delete rows based on 1 condition. I have some data from B12 & down, same for column C,D and E. I'm looking to delete all rows under B12 if they do not have any data. Even if Column C, D or E have data

Thanks

Please help me

I need VBA writing to delete rows, more precisely:

if i have the data as below

DE gross value
DE net value
MX gross value
VZ land
...
I'd like to delete the rows that don't begin with DE, how should I do. My data contains 4000 rows, apparently I can't do it by hand

Thanks a lot for your help
Best regards
oanh

Hello,

I’ve searched and haven’t found anything that will work yet. I was hoping someone might be able to please help me. In Excel 2007 (Vista) I have templates(individual worksheets) for recording details of our purchases for each shopping trip. In these templates the purchases are broken down into various categories (each category has about 15 rows where data can be entered). To enter the data, the item is entered in Col H, if necessary the quantity is changed from “1” in col K the item individual cost is entered in Col L. Col M has formula multiplying cost (col L) times quantity (col)K, for the line total for that item(s). Then in the row below each category group, there is a category subtotal in Col “O”. The entire template is between 500 and 600 Rows depending on the store. Most rows have pre-labeled items for things we routinely purchase, to minimize the entry time.
F G H I J K L M N O
Cat 2 SubCat2 Item Brand Size Qty Cost Total Tax Sub tot
Groceries Dairy Skim Milk Kroger 1/2 gal 1 $0.00
Groceries Dairy 2% Milk Kroger 1/2 gal 1 1.5 $1.50
$1.50

If there isn’t an entry for an item in a particular category, then no item cost is entered in Col L (leaving the cell blank) and the item Total in col M will show “$0.00”.
At the completion of entering this shopping trip , I copy the data from the template into a history spreadsheet. Which is just each template (grouped) , copy and pasted below the previous shopping trip.
Each completed shopping trip will have many rows where the item totals are zero. Because there was no entry for that item. So in the history worksheet, there are a lot of rows that are just taking up space.
I am wanting a macro where I can delete the rows where column M is equal to $0.00

Below are two codes I tried modifying from other forum responses. But the first code ended up with an error trying to run after it deleted rows in the bottom most shopping trip on the history spreadsheet. The error said "Run Time error 13, Type Mismatch"
The first and the 2nd one below, both deleted rows where cells in column M were blank. Not just the ones that indicated $0.00. When I tried to use “$0.00” in the code I received error, guessing the $ sign has special meaning.

So I what I need is code that will delete the row if Col M equals $0.00
But do not delete the Row if Col M is just blank

If I can use this macro while its still on the template page before copy the purchases onto the history worksheet, it may take less time. I noted the first code seemed to loop from the bottom up, and my history page now has around 5000 rows (many of which can be deleted).
If I the code can be written for deleting rows on the template entry page, then I need the code to only delete rows between Row 41 and 585. Rows above 41 have drop down lists built for use in the various categories etc. I can just adjust my reset macro to include copying and paste a new template in place.

Sorry I’ve been so wordy, i'm a gray haired rookie.

Thanks
Keith

1st code tried
Sub DeleteRowsWithTotalequalZeroColumnM()
Dim rng As Range, rng2 As Range
Set rng = Range("M65536").End(xlUp)
Do Until rng.Row = 1
Set rng2 = rng.Offset(-1)
If rng = 0 Then rng.EntireRow.Delete
Set rng = rng2
Loop
End Sub

2nd Code tried
Sub DelZero()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For i = LR To 1 Step -1
If Range("M" & i).Value = 0 Then Rows(i).EntireRow.Delete
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

I am trying to write a code that checks a column, if it doesnt fit the criteria, then delete the entire row.
My VBA is very rusty.

x=6 ( column F)

if len(sheets("sheet1").cells(x,1)

have a macro question. I want to copy and paste data from several files to the last row on a master file. I'm gonna always have data in column C so I can use that to get to the bottom. I have this in the code to get me to the last row
Selection.End(xlDown).Select
That part works, my problem is to paste my data I want to go to the last row plus 1. when I recorded the macro the code had the above line then this
Range("C29").Select
Problem with that is the cell would change with each copy and paste. I don't always want cell C29. I want to paste my data then go to last row + 1. What do I need to put in after the first part which I can get to my last row of data, to go to the next row of data so I can paste?

Copy Data code
Paste data code
Selection.End(xlDown).Select
What goes here to get me to next row

The selection.end takes me to the last row with data, I want the next blank row after that so I can paste again.

Thanks for the help
--Robert

Hi,
I have this code which is supposed to delete rows containing "0" and "null" in all worksheets.
But this is taking too much time and seems like system is gonna hang.

Is there anything we can do to speed up code. Also, its "search all worksheet" function is not working, and is doing work in just the active worksheet.
Please help.

Sub WorksheetLoop()

    Dim I As Long
    Dim sh As Worksheet
    Dim LastRow As Long
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

       'Begin the loop.
        For Each sh In ActiveWorkbook.Worksheets
        
          LastRow = sh.Cells(Rows.Count, "D").End(xlUp).Row
        
           For I = LastRow To 1 Step -1
             If (Cells(I, "D").Value) = "0" Then
                 Cells(I, "D").EntireRow.Delete
             End If
          Next I
            
       

           For I = LastRow To 1 Step -1
             If (Cells(I, "D").Value) = "" Then
                 Cells(I, "D").EntireRow.Delete
             End If
          Next I
            
        Next sh

        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With

End Sub


Hi.
I need a method that delete rows that contains a string ("class" in my case)
the problem is that this code is slow. i need to run it on 15 sheets at least.
Is there a faster way to do this ?
Thanks
Here is my code:
PHP Code: 
Sub DeleteEmptyRows(wsheet As Worksheet)

    Dim calcmode As Long
    Dim ViewMode As Long
    Dim myStrings As Variant
    Dim FoundCell As Range
    Dim I As Long
    Dim myRng As Range
    Dim sh As Worksheet

    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

  
    With wsheet
    Set myRng = .Range(.Cells(1, 1), .Cells(wsheet.UsedRange.Rows.Count,7))
  End With
    Add more search strings if you need
    myStrings = Array("class")
  With myRng
 For I = LBound(myStrings) To UBound(myStrings)
                Do
                    Set FoundCell = myRng.Find(What:=myStrings(I), _
                                               After:=.Cells(.Cells.Count), _
                                               LookIn:=xlFormulas, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                    If FoundCell Is Nothing Then
                        Exit Do
                    Else
                        FoundCell.EntireRow.Delete
                    End If
                Loop
            Next I
        End With
End Sub 


Hi all, sorry for asking this question, but I am new and still learning VBA,
but would like a little help if possible. What I would like to have happen,
is if a cell in column C is blank, or contains a set of numbers like
'1540/06', then it would delete that row. The last two numbers is the day of
the month, so if the last two numbers were not the current day of month, then
it would delete that row. Example below; lets say todays date is the 5th, I
would like it to delete rows 1 & 3, because the date is incorrect in Col C in
row 1, and blank cell in Col C in row 3.

A B C
363 MEM 1450/06
616 BOS 2356/05
225 MEM
455 LAX 1767/05
224 LAX 0540/05

Thanks for any help you could give.

Steve D.

Hello:

I need a macro to delete certain rows in a spread sheet depending on some conditions.

1. If Row contains word "PCheck with # less then 10000 then delete this row plus 2 rows before and 3 rows after. Please have a look at attached sheet.
In this sheet 1st occurence of "PCheck is on row 4 and there is # 995 which is less then 10000 so i would need to delete Row 4 and Row2,3,5,6 & 7.

2. Similiarly as above if row contains work "PDepsoit...delete rows as above.

If possible may be updated sheet can be copied on sheet1.

Once again thank you in advance for all the help i can get...

Hello,

I need help with the following conditions. I have attached a sample file with desired results.
Conditions required to delete row:

1 Delete Row when cell E is blank (example: Row 6 will be deleted as E6 is blank)
2 First character in column 'C' is an alphabet (for example: Row 5 will be deleted as the first character in the cell is an alphabet
3 First character in column 'B' is an alphabet (for example: Row 29 will be deleted as the first character in the cell is an alphabet
Special Notes : All characters in column 'B' are not in number format (ex: 555-9999 is general)
There are more than 1500 rows of data that need to be sorted with the above conditions

Thanks

Hi and thanks in advance.

I need some VBA that will delete rows on a sheet based on user selection from a form listbox.

If the user selects one or more columns from the listbox then I want to delete the rows on the active sheet that have blank cells in those columns.

So if the user selects columns A, E, F and a row has blanks in all three of those columns, then I want to delete it.

I currently have this code with removes rows that are completely blank ...

For i = TheRange.Rows.Count To 1 Step -1
   If WorksheetFunction.CountA(TheRange.Rows(i)) = 0 Then
      TheRange.Rows(i).EntireRow.Delete
   End If
Next i
thanks
Deutz

I am trying to delete rows from a spreadsheet that are almost duplicates. For instance, rows 1 and 2 are the same except that the number in column f is negative in row 1 but positive in row 2 (e.g., -1 and 1). I have thousands of rows and am looking for an easy/fast way to delete these "almost dupes" without having to do so manually. Any ideas? Thanks in advance. I've attached a sample file to show what I mean.test cases.xls

I want to write some VBA code which will write formula into several rows - however, I want it to select which rows it writes the code into depending upon the following

Sheet1

(Row Number) ID Price
1 1234 (Formula which will be written if applicable)
2 4321 (Formula which will be written if applicable)
3 5678 (Formula which will be written if applicable)

Sheet2
ID
1234

In this case, I would only want to write the formula into line 1 in sheet1.

is this possible? maybe an If statement or something or a For Each Rows = 1 to X????
Cheers.


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