Free Microsoft Excel 2013 Quick Reference

VBA Macro Code To Delete Row If Value Collected From User Is Less Than Cell Value

Hi all excel fans

I have a very simple code which is to delete the row based upon an input box entry. If the value of cell A1 is less than the input box entry, then row 1 is to be deleted. If the value of cell A1 is greater than the input box entry, then no row should be deleted.

It seems like a pretty straight forward request, but it doesn't matter what I enter into the input box - it deletes row 1 and my IF statement using the less than operator does not appear to work. Very frustrated.....please see my code below. Am I missing some basic concept?

Thanks
Russel


	VB:
	
 deleterow() 
     
     
    Amount = InputBox("Enter Value") 
    Sheets("TestS").Activate 
    ActiveSheet.Range("A1").Select 
     
    CellValue = ActiveSheet.Range("A1").Value 
     
     
    If CellValue < Amount Then ActiveCell.EntireRow.Delete 
     
     
End Sub 

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


Hi, this question is related to another Auto Filter macro question I had last
week (see below for the URL if interested).

I think the title of this question is clear enough. I have about 2000 rows
of data. I would like to delete all the rows where the Value in any cell in
Column V is less than 2.5.

How do I do this? Thanks very much!

Regards
Steve C

http://www.microsoft.com/office/comm...xp=&sloc=en-us

Hey i made this code to delete all rows that have value 7 in column L, but it only deletes one at a time. That means, the first time i run it, it deletes one row. The second time, it deletes another one. What is wrong with my code?


	VB:
	
If Sheets("Aktivni").Cells(1, 1).Value = vbNullString Then 
    Exit Sub 
End If 
 
Dim rngax, cellax As Range 
Dim SteviloVrsticAktivni As Integer 
SteviloVrsticAktivni = Worksheets("Aktivni").Range("L:L").Cells.SpecialCells(xlCellTypeConstants).Count 'counts rows
Set rngax = Sheets("Aktivni").Range("L2:L" & SteviloVrsticAktivni) 
For Each cellax In rngax 
    If cellax.Value = 7 Then 
        Rows(cellax.Row).Select 
        Selection.EntireRow.Delete 
    End If 
Next cellax 
 
End Sub 

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


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.

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

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 need to create a macro to delete a row if the value is less than a number
that I specific

I need to create a macro to delete a row if the value is less than a number
that I specific

i have a large spreadsheet with certain rows i need to delete if they have certain strings of text. i need coding to delete rows only if they contain any of the various certain strings of text as a whole. i found code that deletes rows if any single word is found, but i need it to find only exact words together (e.g. county of residence, - not just if any of those words are found elsewhere like when "of" is found in "zip code of residence"

so: i need to delete rows that contain "county or residence" or "age of respondent" or "zip code of employment"

I also need it to search 2 different columns rather than just one.

(i think macros would be the easiest solution??)
any help would be appreciated! thanks!

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.

If date in column A is less than 15th of month is there a formula I can use
to display the next mth in column B such that I can obtain the following

Column A Column B
05-Oct-07 Oct-07
05-Nov-07 Nov-07
15-Oct-07 Nov-07
19-Nov-07 Dec-07

Thanks
--
Angela

I export data from Quickbooks into excel. Quickbooks unfortunately does not let me filter the data with two variable, because if should be <>0. In Quickbooks you can only filter on = or > or <.
So I export this huge data block into excel to then import into access.

When the data is imported into access, I run a macro which massages the data into a format access can use.
I now have more then 10,000 rows to copy and it has reached the limit of some of the computers memory.
Out of the 10,000 or more transactions, there are more 4,000 items with the value of zero, which I can get rid of.
I would like to be able to add some lines to go through the rows and delete all rows where the value in column J=0.
Thank for any help.
Anne

Hi All,

I'd like to delete the rows in column K if the value is between -10,000 or +10,000. I found some code from en earlier post that I've been trying to work with, but can't seem to quite get it.

Here is the code:
Dim Rng As Range
Dim calcmode As Long
    
    With Application
         calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
  
    With ActiveSheet
        .AutoFilterMode = False
        
        .Range("K2:k729" & .Rows.Count).AutoFilter Field:=1, Criteria1:=">=(10000)",
Criteria2:="<=10000"
        With .AutoFilter.Range
            On Error Resume Next
            Set Rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not Rng Is Nothing Then Rng.EntireRow.Delete
        End With
      
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
Thanks in advance for the help.

Cheers

Hi guys, I believe this will be fairly simple, but I am a noob at VBA so I don't know how to do it.

I am using Ron de Bruin's code to delete a row if a certain criteria is met. The crucial part is:

Firstrow =
.UsedRange.Cells(1).Row
        Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

        '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 = "ron" Then .EntireRow.Delete
                    'This will delete each row with the Value "ron"
                    'in Column A, case sensitive.

                End If

            End With

        Next Lrow
How can I modify it so that it will delete the row only if the value in column "A" AND the value in column "B" are "ron"?

Thanks in advance!!

Hi guys.

I have a sheet with data in columns A through to E.
In column E are the values of funds in accounts. Not all cells have values though, some have text or symbols and these are interspersed amongst the data.
I need my macro to delete all lines that do not equal zero.

I've tried using this code, but it doesn't delete anything. I'm guessing this is because of the cells which are not value cells
   Do
    If ActiveCell.Value = "0.00" Then
    ActiveCell.EntireRow.Delete Shift:=xlShiftUp
    ElseIf ActiveCell.Value <> "0" Then
    ActiveCell.Offset(1, 0).Select
    End If
    Loop Until IsNull(ActiveCell.Value) Or ActiveCell.Value = 0
I've also tried this code, this deletes most of the 0 value rows, but for some reason, doesn't delete the bottom 2.
    Range("E2:E1000").Select
    For Each cell In Selection
    If cell.Value = "0" Then
    cell.EntireRow.Delete
    End If
    Next cell
    Range("A1").Select
If I put that code in the macro twice more, then it delete the final 2 lines. So I know it isn't that values of the cells themselves causing this problem.

In a slight change to the code, I'm trying to use this code to delete rows where the data in column A reads GBP
    Range("A2:A1000").Select
    For Each cell In Selection
    If cell.Text = "GBP" Then
    cell.EntireRow.Delete
    End If
    Next cell
    Range("A1").Select
However, it doesn't appear to delete anything.

Any ideas why I just can't get these pieces of code to work?

Hi there,
I'm after a simple VBA code to delete a row if the number in a certain cell is a specific number or move to next row down if it's not.

eg.
91E1 0 642700 9746500 4
101E1 0 642725 9746500 3
91E1 0 642700 9747500 4

If the value is a 4 (in coloum D) delete the entire row
(91E1 0 642700 9746500 4)
and move onto the next one down. If it's a 3 then leave it and move to next.

Thanks
mpw

Hi,

I have a list of data that contains some rows that I dont want. These rows aren't necessarily in order.
Basically, where the value in column A of that row is LESS THAN 3 characters long, I want to delete the row.
I have done the following routine that does this, but as it deletes a row, it doesn't check the immediate row below, as the loop doesn't take into account the deleted row (does that make sense?)

Code:
Sub delrow()
alan = Sheets("Sheet1").UsedRange.Rows.Count
For Row = 2 To alan
    If Len(Range("A" & Row).Value) < 3 Then
    Rows(Row).Delete Shift:=xlUp
    End If
Next
End Sub
I either need a simple fix to make this simple loop take into account when it deletes a row, the next row is infact the one it is looped on, or I need a better way of deleting all rows in a range where the Cell range("A" & row#) is less than 3 characters.

A solution for the loop taking into account the deleted row(s) would be nice anyway, and if some better code is mentioned also, that would be a bonus

Thanks in advance
Alan

I have macro code to hide rows based on a certain critera but I don't know how to adapt the code to delete the rows. Basically it checks a column and for every row

Hi There,

I need a macro code to do the below task (i've attached a sample workbook for your reference). please see below for the requirements

1 step - Copy sheet1 data (column B5 & C) - No. of rows to be copied should be dynamic.
2 step - Place the copied data into Sheet2 (into column A & D)
3 step - Match sheet2 Column A values with Sheet3 Column A, and if matches, copy the matching values from sheet3 column B & C and paste them into sheet2 Column B & C
4 Step - Once all the lookup is done, sort the table in sheet2 by column "Traffic"

(In short, I'm trying to join the tables by creating a seperate sheet called "Sheet2").
If you look at the attached file, then you will have a better idea.

For looking up values, "Keyword" is the lookup value

Also, I'm a very beginner to VBA, so i might need detailed instruction.

Appreciate your help.

Best regards,
Prithvi

Hi everyone,
Any suggestions on how to delete rows by VBA?
The condition is, If the value on Cell (From D7 to last record) is equal to "#VALUE!"
It will delete the entire row.
Appreciate your help once more. Thank you in advance.

Andrew

my workbook contains 3 worksheet which are password protected to hide the formula and the password is knird@123
Worksheet name are t-2011,t-2025,t-1122.
There are vba code and other trick also by which worksheet protection can be unlocked.
How to create macro to delete all the worksheet as soon as worksheet protection get disables.

It bit tough for me.

Thanks in advance.

Hi all you've been a great learning tool for me,
I've been searching the last hour to find a solution to my problem and somehow I got banned (this is not my 1st post) so I had to create new logon.

Anyway I need a macro which will look at the cells in Column A and if the first character in the cell is a letter or symbol...then delete the entire row. If the first character is numeric..move the the next row and keep going. By symbol I mean a little square box that came from converting it from text to excel.

My data looks like this
a1 = region
a2 = 12-ba-13
a3 = 1-800-444
I need to delete row 1 and keep rows 2&3

I tried w/o a macro by using =isnumber(left(a1,1)) but it didn't work as I think it converted it to text. I kept getting false, so I figured the masters here could conjur up a macro to do it for me which would eliminate steps anyway.

Thanks !!

I want to use Ron de Bruin's
http://www.rondebruin.nl/delete.htm
AutoFilter code to delete a lot of rows.
The criteria for rows to be deleted is:
=OR(NOT(ISNUMBER(B9));AND(ISNUMBER(B9);ISTEXT(C9)) ;LEFT(D9;4)="Side")
from row 9 to row 30.000.

Any help on how to put this formula into Ron's below code:

Regards
Hans Knudsen

Sub Delete_with_Autofilter()
Dim DeleteValue As String
Dim rng As Range
Dim calcmode As Long

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

'Fill in the value that you want to delete
'Tip: use DeleteValue = "ron" to delete rows without ron
DeleteValue = "ron"

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1,
Criteria1:=DeleteValue

With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With

End Sub

Hi, i need a macro to delete empty rows if a cell in A3:A200 is blank.
Ex:
A B C
3 FILL FILL FILL
4 FILL FILL
5 FILL FILL FILL
6
7 FILL FILL FILL
In this example, i need to delete row 4 and 6.
Can this be done?
Thanks!

Hello forum,
I'm hoping someone will be able to help me with this. I am a novice and havent used loops in vba before. I suspect using a for/next loop will be the solution to the problem I am about to describe.

It's a bit of a convoluted problem, but the data output from the Macro needs to be entered in a certain format in order for it to be useful in the program I intend to use it in.

I have attached a spreadsheet of my data to make description easy:

What I'd like the Macro to do is:
1) Extract the entry from sheet 1 cell C2 and D2, then paste it in C1 and F1 on sheet 2 (respectively)
2) Extract the entry on sheet 1 cell E2 and F2 and paste it in C2 and F2 on sheet 2
3) Fill sheet 2 cell D1 and G1 with 3 (number) and D2 and G2 on the same sheet with 0 (number)
4) Fill sheet 2 cell H1 and H2 with -1 (number)
5) Fill sheet 2 A1 with the text string MPC
6) If the number in sheet 2 cell C1/F1 begin with 5, B1 etc should be 101, otherwise, it should be 102

A Sample of the (one) final entry for each line is shown in the sheet “sample”; basically, I want the code to repeat for all values in sheet 1 and stop

Any help will be appreciated.

Thanks