Macro to delete rows with certain values 1

[DSerr77]

I am trying to write a Macro that will evaluate the contents of a cloumn and delete the entire row if the value in a cell meets a certain criteria. Here is what I have so far:

Sub Delete_Row()
Dim Mvalue
Range(A1).select
Do Until Mvalue = Null
Mvalue = ActiveCell.Value
If (ActiveCell.Value = 2)Then
'(I would like to be able to control this value)
'Need to know how to select and delete row
End If
ActiveCell.Offset(1,0).select
Loop

End Sub

Appreciate any help

 

[CBasicAsslember]

Sub test()
Dim vColTemp As Variant ' Temp variable that holds all the values in a column
Dim rowcount As Long     ' row counter
Dim colcount As Long      ' column counter
Dim iLookupValue As Long ' Value you want to find
Dim x As Long


colcount = 3 ' Selects the column
iLookupValue = 2 ' Value you want to find

' Sets rowcount equal to the number of rows in the column
rowcount = Sheet1.Cells(Rows.Count, colcount).End(xlUp).Row

' Load vColTemp with column values
vColTemp = Sheet1.Range(Cells(1, colcount), Cells(rowcount, colcount))

' Loop through all values
For x = rowcount To 1 Step -1
    
    If vColTemp(x, 1) = iLookupValue Then
    
        ' Delete column
        Sheet1.Rows(x).EntireRow.Delete
    End If
Next

End Sub

 

[DSerr77]

That is awesome!! I am greatful for your help. If I may impose one more time, is it possible to be able to change the lookup value without changing the code each time can this be a input from the user? Thanks again.

 

[CBasicAsslember]

is it possible to be able to change the lookup value without changing the code each time ...

Not sure what you mean. You can change the iLookupValue without having to change the loop. If you want to do 2 or more values at the same time just put in another If statement in the loop with a different iLookupValue. If you wanted 3 values you could have, for example, iLookupValue1, iLookupValue2, and iLookupValue3. You would then have:

If vColTemp(x, 1) = iLookupValue1 Then
    
   ' Delete column
   Sheet1.Rows(x).EntireRow.Delete
End If

If vColTemp(x, 1) = iLookupValue2 Then
    
   ' Delete column
   Sheet1.Rows(x).EntireRow.Delete
End If

If vColTemp(x, 1) = iLookupValue3 Then
    
   ' Delete column
   Sheet1.Rows(x).EntireRow.Delete
End If

... can this be a input from the user?

Sure. If you wanted to use a cell for the user input and assume the cell is A1:

iLookupValue = Sheet1.Range("A1").Value

 

[DSerr77]

Ok so I had this working in a test environment. Then I moved it to the real location (had to change the sheet name) and now the code continually runs no error, just no action. Here is what I have:

Sub DelRow()
'
' DelRow Macro
' Macro recorded 12/1/2006 by Frontier
'
Dim vColTemp As Variant ' Temp variable that holds all the values in a column
Dim rowcount As Long ' row counter
Dim colcount As Long ' column counter
Dim iLookupValue As Long ' Value you want to find
Dim x As Long
'
Sheets("Pivot Data").Activate
'
colcount = 7 ' Selects the column
iLookupValue = Sheets("Controls").Range("B9").Value ' Value you want to find

' Sets rowcount equal to the number of rows in the column
rowcount = Sheets("Pivot Data").Cells(Rows.Count, colcount).End(xlUp).Row

' Load vColTemp with column values
vColTemp = Sheets("Pivot Data").Range(Cells(1, colcount), Cells(rowcount, colcount))

' Loop through all values
For x = rowcount To 1 Step -1

If vColTemp(x, 1) = iLookupValue Then

' Delete column
Sheets("Pivot Data").Rows(x).EntireRow.Delete

End If

Next
'
Sheets("Controls").Activate
Range(A1).Select
'
End Sub

 

[CBasicAsslember]

It works for me. Are you sure you have the right column (7 is column G)? Also, the line before you exit the sub:

Range(A1).Select

Should be:

Range("A1").Select

Step through the code (F8) and watch what is happening to your variables in the Locals window.

 

[DSerr77]

G is the correct column, could it be something with the way G is formatted (ie Text vs Comma) It seems to run in an endless loop.

 

[CBasicAsslember]

I just did it with the column formatted as text and it worked. Did you check the column formatting? Are the numbers actually integers where the formatting doesn't show any decimal places?

 

[DSerr77]

I have tried this so many times and have had numerous people look at it. Evryone thinks it should work and the original code does work, could it be something in the sheet name?

 

[CBasicAsslember]

The best thing to do is step through it and make sure all the variables are right.

 

[AnotherHiggins]

Hi,

Stepping in a bit late, but if you're still having trouble, I'd like to suggest a different technique altogether: using CountIf.

This will allow you to just loop through each row, not each cell, and should therefore run faster.

Give this a shot:

Sub delete_rows()
Application.ScreenUpdating = False

LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
MyVal = InputBox("What do you want to delete?", "Delete Variable")

For i = LastRow To 2 Step -1
If Application.WorksheetFunction.CountIf(Range(i & ":" & i), MyVal) > 0 Then _
    Rows(i & ":" & i).EntireRow.Delete
Next i

Application.ScreenUpdating = True
End Sub

That will allow you to enter the value into a box that pops up. OR, if you would rather use a value on a given sheet, then go with what CBasicAsslember suggested:
code]MyVal = Sheet1.Range("A1").Value[/code]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DSerr77]

John,

That code works great in a test file, the minute I try to put in in the real file it goes belly up. It looks like a continious loop. Similar to the problem I mentioned before. Any idea what could cause that to happen? Thanks.

 

[CBasicAsslember]

Have you stepped through either John's code or the code I provided?

 

[PHV]

It looks like a continious loop
What is the value of LastRow ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DSerr77]

If you mean Row 65000, The value is null. If you mean of the table the value is 11. However, I have made a minor discovery. I think I have too much data and it just bog the function down. Any suggestions?

 

[DSerr77]

I need to delete approx 25,000 rows at a time. I think this may be too much too handle.

 

[AnotherHiggins]

PH means what is the variable "LastRow" set to in the code?

Step through the code one line at a time using F8. After you pass this line:
LastRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
hover the cursor over the word LastRow in the code to see what value it contains.

If you have several thousand rows it might take a while to run the code, but it will run.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PHV]

I meant: what is the value of your variable named LastRow when the VBA code enters the loop.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

I need to delete approx 25,000 rows at a time. I think this may be too much too handle.

That is unlikely to be too much unless you have a pretty old computer. How long it takes to run and whether your computer can do anything else while crunching 25,000 records depends on what kind of system you have. I just ran a test on an oldish IBM Vista with 256K Ram running windows 2000, Excel 2000 and it took about 10 minutes to run that code on 25K records.

You could save time if you could avoid a loop...

I just got myself thinking about that, and I think we CAN avoid a loop. Let's say you have data in columns A:C, headers in row 1 and data in rows 2:25000. This code should do what you want. Let us know if you need help tweaking it for more columns.

Sub a_test()

Application.ScreenUpdating = False

Range("A2").Select

[green]'Field is the column number you want to check. Field 1 = colA.[/green]

Selection.AutoFilter Field:=1, Criteria1:="2"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete
Selection.AutoFilter Field:=1

Selection.AutoFilter Field:=2, Criteria1:="2"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete
Selection.AutoFilter Field:=2

Selection.AutoFilter Field:=3, Criteria1:="2"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).EntireRow.Delete
Selection.AutoFilter

Application.ScreenUpdating = True

End Sub

I just did another test: the same computer ran that code in about 1.5 minutes on 25K records.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DSerr77]

The filter method looks good but I need that variable to change. My criteria needs to be dynamic.