Free Microsoft Excel 2013 Quick Reference

VBA Macro Code To Delete Each Row With Specific Value In Specific Column

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


Post your answer or comment

comments powered by Disqus
I found the following code on online:

"Delete rows with specific value in Column A and on same row specific value in column B "

   Sub Delete_rows_based_on_ColA_ColB()
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual
      Dim cell As Range, rng As Range, i As Long
      Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues)
      For i = rng.Count To 1 Step -1
         If LCase(rng(i).Value) = "standard" _
              And LCase(rng(i).Offset(0, 1).Value) = "card" _
                Then rng(i).EntireRow.Delete
      Next i
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
    End Sub

I need help recoding it (if possible) so that it looks at all rows and if Column F, G, & H are <=0 then the row gets deleted, but all three columns (F, G, & H) MUST be <=0

As a side request, I cant figure out in the original code where you would enter the "specific value" to look for in A & B.

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


I just took on a client who sends me a multipage spreadsheet weekly with movie times for his 10 theatres.
My guess is he just deletes the information from the previous weeks row and adds a new row. His sheet really only needs about 16 columns and maybe 20 rows. The sheets he sends have upwards of 1500 rows and columns.
Many of the rows are in between the actual text rows as well.

I convert this to a webpage and post to his site. Problem is the sheets are so large they take forever to clean up (in GoLive) and much too long to do by hand.
Is there an easy way to delete all rows with no text in them?

thank you in advance for any help.

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.

Is it possible to make a macro that will delete rows from two colums worth of
data? It would need to delete any rows that have empty cells in column A.

--------------------------------------------------------------------------------

A B
CLEMSON UNIVERSITY
CLEMSON UNIVERSITY
CLEMSON UNIVERSITY Count 2
CMS INC.
CMS INC.
CMS INC.
CMS INC. Count 3
COASTAL TRAINING
COASTAL TRAINING Count 1
COHASSET ASSOC
COHASSET ASSOC Count 1
COMFORT INNS
COMFORT INNS Count 1
Commonwealth of
Commonwealth of Count 1
COMP SYSTEMS, INC.
COMP SYSTEMS, INC.
COMP SYSTEMS, INC. Count 2

--------------------------------------------------------------------------------

I would want the macro to delete all rows with no data in column A. There's
1,700 rows of data, so I figure a quick macro would save me time.

Any ideas?

Thanks.

Hi,

I'm trying to write a macro that will delete all rows where the value in Column in C is the same as the one above it BUT not if the value is a blank row. Here's what I have but it keeps erroring on me....can anyone tell me what I'm doing wrong. Thanks!

Do Until lastrow = 1
If Cells(lastrow, 3).Value "" Then
If Cells(lastrow, 3).Value = Cells(lastrow - 1, 3).Value Then
Rows(lastrow).Delete
End If
End If
lastrow = lastrow - 1

I have been trying to transpose a piece of code that was put on this forum back in 2005 into my own Workbook but have been unsuccessful. The original piece of code filtered a column for unique entries and then filtered the main sheet for each unique entry and cut and paste each row that the unique value occurred in and in a new worksheet. A new worksheet would be created for each unique filter.
So what I am trying to do is look up a list of values that i have in a list on the worksheet. Then for each entry in my list, create a new page and cut and pasted the filtered main worksheet into that page.
Finally return to the main worksheet and then remove all filters.

I have attached the original file that was submitted so you can look at how it works and I have posted a sample my worksheet (with some things disabled to reduce file size but hopefully you will get the layout. However I don't currently have that sample on this computer. It is posted on another thread under the title "Selecting rows with specific values in a column".
Thanks for any help or thoughts.

Cheers

Hi there all. I'm new here, and you've got some very useful information here. I hope that you can help with what appears to be a simple issue. I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.

I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.

Any suggestions for the macro?

Hello,

With my limited VBA experience I am trying to write VBA code that searches each row with information and when it finds a certain cell in a row (ie: yes or a date), then I want it to execute a condition.

I am having no luck as the code I write either searches and doesn't trigger the condition or it triggers the condition even if a cell has no data in it.

Help!

hello everyone,

I want to make a that creates a button on different rows using the following code:


	VB:
	
 
For x = 3 To 15 
    With Range(Cells(x, 1), Cells(x, 1)) 
        ActiveSheet.Buttons.Add(.Left, .Top, .Width, .Height).Select 
        ActiveSheet.Buttons.Text = "Delete" 
        ActiveSheet.Buttons.VerticalAlignment = xlBottom 
    End With 
Next x 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I select one of these buttons I want it to select the entire row and delete it and also delete the button itself.

How can I get the button to find the row in which it is placed?

I tried the following:


	VB:
	
 Mainscoresheet() 
     ' Mainlineup Macro
    Dim b As Object, cs As Integer 
    Set b = ActiveSheet.Buttons(Application.Caller) 
    With b.TopLeftCell 
        cs = .Row 
    End With 
    MsgBox "row Number " & cs 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
but it gives the following error: "Unable to get the Buttons property of the Worksheet class"

anyone have an idea how to make this work?

regards,

Hi guys,

I would like to select every row in my worksheet which contains cells with bold characters. Here is my code:


	VB:
	
 Macro3() 
    Dim rngA As Range 
    Dim cell As Range 
     
    Set rngA = Range("A1", "G110") 
    For Each cell In rngA 
        If cell.Font.Bold = Bold Then 
            cell.EntireRow.Select 
        End If 
    Next cell 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My problem is that this code only selects me the last found row with bold characters. Any ideas how can I extend it to select all the rows and not only the last one? Thank you!

Attila

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


I have a range of data, A1:D500 with Column D containing numbers. I would like VBA code to delete all rows in which the value of Column D is 0. Note: there can be instances where Column D is blank, and I would rather not delete these rows (if possible).

Thanks for the help.

Hi,
I don't really know much about VBA code, I am trying to delete duplicate rows based on 2 columns criteria. I have data from column A until column BC and the columns that determine whether the record is duplicate or not are column K and AJ, here's the sample:

Column K Column AJ
ID Amount
AB1234 $1,220
AB1234 $0
AB1234 $0
AB1234 $500
AF1122 $750
AC3344 $0
AG1133 $2,500

As you can see there are some duplicates with ID AB1234, I would like to delete if the ID and Amount are duplicated, so I will end up like this
Column K Column AJ
ID Amount
AB1234 $1,220
AB1234 $500
AF1122 $750
AC3344 $0
AG1133 $2,500

I would like to be able to run a macro to perform this.

Thank you so much in advance! Auto Merged Post Until 24 Hrs Passes;

After browsing this forum, I found some Vba codes, it was posted by Trebor76, but the code doesn't delete the row with duplicate IDs in column K and $0 in column AJ. The sample above, after I run the code, here's the result

Column K Column AJ
ID Amount
AB1234 $1,220
AB1234 $0 this row should be deleted also
AB1234 $500
AF1122 $750
AC3344 $0
AG1133 $2,500

In summary, I want to be able to delete duplicate IDs with same dollar amount and leave the top row or leave 1 record. If the IDs are duplicated and there are $0 and >$0 amount, delete all the $0 and leave the ID with >$0.

Here's the code that I found:


	VB:
	
 DeleteDuplicateRows() 
     
     'Cell K2 will be the starting position.
    Range("K2").Select 
     
     'Keep running until the active cell is blank.
    Do Until ActiveCell = "" 
         
         'If the concatenation of the active cell and the cell to its' immediate right equals
         'the concatenation of the cell above and and the cell to its' immediate right, then...
        If ActiveCell.Text & ActiveCell.Offset(0, 25).Text = ActiveCell.Offset(-1, 0).Text & ActiveCell.Offset(-1, 25).Text
Then 
             '...delete the row.
            Selection.EntireRow.delete 
             'Else...
        Else 
             '...move down to the next cell.
            ActiveCell.Offset(1, 0).Select 
             
        End If 
    Loop 
End Sub 

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

Need VBA code that remove rows with the same # in column B whose row's amounts in colum M total to -0-, starting with row 6. For example,

__ColB_ColM
06 312 3.20
07 378 2.24
08 378 (2.24)
09 404 7.54
10 404 2.10
11 404 (9.64)
12 621 1.55
13 621 3.53
14 621 (4.33)

Needed Result:
__ColB__ColM
06 312 3.20
07 621 1.55
08 621 3.53
09 621 (4.33)

In other words, VBA to remove lines with the same number in column B, whose amounts equal -0- in column M. For example the following lines should be deleted from above because their column M amounts equal .00:

09 404 7.54
10 404 2.10
11 404 (9.64)
Total = .00

07 378 2.24
08 378 (2.24)
Total = .00

Thanks for all your help! mikeburg

Crosslink: http://www.excelforum.com/showthread.php?t=647342

I have a macro that takes several minutes to run and I've isolated the problem to the below code snippet that deletes each row if the value in column 4 is not equal to "1". I have similar code called throughout the macro. It works, but is very slow. Is there a more efficient way to delete rows based on value?

Code:
 With ActiveSheet
            lastRow = Cells(Rows.Count, 1).End(xlUp).Row
                For i = lastRow To 5 Step -1
                     If .Cells(i, 4).Value  "1" Then
                        Rows(i).Delete Shift:=xlUp
                     End If
                Next i
 End With


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

I am using this code to delete rows which contain "perm" and "Client
EndDate" .
I also wish to delete rows before now and after now + 30 days.
I've tried all sorts of combinations without success. Can somwone help a
relative newcomer to VBA

Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then ' Do nothing
'++++++++++++++++++++++
' This will delete each row with the Values in Columns A and C, case
insensitive.
ElseIf Trim(LCase(.Cells(Lrow, "C").Value)) = "perm" And _
.Cells(Lrow, "A").Value = "Client EndDate" Then .Rows(Lrow).Delete
'+++++++++++++++++++++++++++++++++++++++++
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With

I have several spreadsheets in which 4 of every 5 Rows needs to be deleted.
ie: Row
1 is good, Row 6 is good, Row 11 is good, Row 16 is good, etc. I need to
delete Rows 2-5, 7-10, 12-15, etc. I would prefer to set a variable in the
macro to tell it how many sets of 4 consecutive rows I need deleted (with one
good row between each bad set of 4). The rows that need to be deleted are not
entirely blank, some of the cells have data (not needed) but there are a few
rows that are entirely blank. It needs to delete the rows irreguardless of
any data in that row. Help is appreciated as I am not a programmer but you
guys are really good.

OR

Every 5th cell in column A has data. I need to delete all ROWS that have no
data in COLUMN A(even if it is a "space" that was used to delete previous
data) in column A. How would it know when it reached the end of the data and
continue to delete the balance of the blank spreadsheet?

Thanks
Danny

Hello,

I am having trouble with the amount of time it takes to separate unneeded data from a report that I run daily and was wondering if there is any code that may simplify things for me.

The report I run has many (300 or more) agent names in Sheet 1, Column A and I have to manually remove the unneeded names before continuing with my reporting.

What I would like to do is utilize code to delete the rows with the unneeded agent names on Sheet 1, Column A from a list of the "unneeded agent names" that I will create on Sheet 2, Column A.

Any suggestions or information on how I might get this done without the lengthy manual process would be great! Thank you in advance for your advice.

Eric

I am using the following VBA to delete a row with a date in column D.
There MAY be other rows with the same NAME in column B but without a date in D. How do I subsequently delete that row


	VB:
	
Application.Calculation = xlCalculationAutomatic 
Application.ScreenUpdating = True 
 '''''''''  Delete last day worked row''''''''''''
Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 
Set rng = Columns("A").SpecialCells(xlConstants, xlTextValues) 
For i = rng.Count To 1 Step -1 
     
    If LCase(rng(i).Offset(0, 3).Value)  "" Then rng(i).EntireRow.Delete 
     
Next i 
Application.Calculation = xlCalculationAutomatic 
Application.ScreenUpdating = True 

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


Hi.. I've been having a bit of a problem with this sheet..

I know it sounds simple and I've searched for it in the forum, but it doesn't work in my sheet..

I need a macro to delete entire rows that have data in column B but have no data (apparently empty) in column C. Looks like column C doesn't have data, but I've tried to click on a cell a hit delete and tried the macro again and then it deleted the entire row. This means that there's actually something invisible in the cell, like a space or something, once those cells were copied and pasted special from another sheet.

Is there a way to ignore the fact that it has some "invisible" data, and delete the entire row with a macro anyway?

Attached goes an example.

Thank you

I am looking for a macro to delete all rows with #REF! appearing in column B between rows 8 - 100.

Please help! Thank you!

Can someone please help me with some code.

I need to build a macro, which will delete all rows with a specific value in column A, only if column B does not equal a different specific value. I can't seem to find any relevant posts. thank you very much.

-p


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