Module to delete rows

[john3j]

I need to create a module to delete entire rows if whatever is in column 4 does not begin with J. Typically, I think it would be best for it to count up the worksheet. There are only 6 cases that should pass. I dont know if a select case would be best. Basically, I have divisions, we will call them JAA, JAB, JAC, JAD, JAE, and JAF. If column four does not meet any of these divisions, the whole row needs to be deleted. Any help would be greatly appreciated. Also it would need to run on two sheets in the workbook. We will just call them "Sheet1" and "Sheet2". Thanks again.

[Bob Phillips]

'-----------------------------------------------------------------
Public Sub DeleteRowsUsingAutofilter()
'-----------------------------------------------------------------
' Function: Conditionally deletes rows
' Synopsis: Calculates the number of rows
' Creates a filter
' Filters by condition
' Sets a range object to visible cells in target column
' If any cells visible, deletes entire row
' Assumptions: None
' Author: Bob Phillips - © 2010 xlDynamic.com
'-----------------------------------------------------------------
Dim LastRow As Long
Dim rng As Range

Application.ScreenUpdating = False

With ActiveSheet

.Rows(1).Insert
.Range("D1").Value2 = "temp"
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
Set rng = .Range("D1").Resize(LastRow)
rng.AutoFilter field:=1, Criteria1:="<>J*"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

Application.ScreenUpdating = True
End Sub

[Ted]

Hi Xld,

I have a DDE referenced sheet which executes code under calculate() module.

When I insert the delete code in calculate() module, it does not work? That sounds silly but do you have any idea.

Thanks

[Bob Phillips]

Not immeditaely Ted.

Are you saying that after the DDE has completed if you add that code nothing happens?

And what do you mean by the Calculate module?

Maybe post the workbook for us to see.

[john3j]

XLD or anyone else who can help. I have attached a test workbook. It seems I was wrong when stating which column the items beginning with "J" were in. It needs to work on column "H" now. I tried modifying the code myself, but I have screwed it up so I took it out of the workbook for someone to start fresh. Remember it is supposed to go through column "H" and delete all rows that do not have an entry starting with "J" in the Division column.

Thanks!

[john3j]

also, can assume that there are 4 or 5 sheets in the workbook and want it to run on 2 of the sheets. lets just call them sheet1 and sheet2.

Thanks

[Ted]

Hi John3J,
I think you have to do the following, from original code,
Set rng = .Range("D1").Resize(LastRow)
change to
Set rng = .Range("H1").Resize(LastRow)

[Ted]

Hi Xld,

I am attaching a test file. I want to keep most recent 10 rows in Results worksheet. So whenever number of rows are > 10 then I delete the Row#1. It sounds so simple :S

This is what I am adding in my code but it doesnt work :S


If ActiveCell.Row >= 10 Then
Range(Range("A1"), Cells(ActiveCell.Row, 1)).EntireRow.Delete shift:=xlUp
End If

[Bob Phillips]

Your example isn't much use, no code to check, and less than 10 rows of data.

[Ted]

Right click on Results sheet and click view code. You can see the code under Microsoft Excel Objects->Sheet2 (Results)

If you enter any value in A1 in DDEInput, it adds it in new row in Results sheet. I want to stop adding new rows at lets say row 5 or 10 and only keep the most recent updated values in Results sheet.

[Bob Phillips]

Private Sub Worksheet_Calculate()

Dim LastCell As Long
Dim i As Long

If Range("H1").Value <> 0 Then

With Worksheets("Results")

LastCell = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastCell To 11 Step -1

.Rows(i).Delete
next i
End With

Worksheets("DDEInput").Range("A1").Copy _
Destination:=Worksheets("Results").Range("A" & LastCell.Row)

End If

End Sub

[john3j]

Ok, here is the code I am using:

'-----------------------------------------------------------------
Public Sub DeleteRowsUsingAutofilter()
'-----------------------------------------------------------------
' Function: Conditionally deletes rows
' Synopsis: Calculates the number of rows
' Creates a filter
' Filters by condition
' Sets a range object to visible cells in target column
' If any cells visible, deletes entire row
' Assumptions: None
' Author: Bob Phillips - © 2010 xlDynamic.com
'-----------------------------------------------------------------
Dim LastRow As Long
Dim rng As Range

Application.ScreenUpdating = False

With ActiveSheet

.Rows(1).Insert
.Range("D1").Value2 = "temp"
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
Set rng = .Range("H1").Resize(LastRow)
rng.AutoFilter field:=1, Criteria1:="<>J*"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With

Application.ScreenUpdating = True
End Sub

I made the change suggested by xld, but I am still getting an error when I run it. Debugger is stopping at line the line that says:

rng.AutoFilter field:=1, Criteria1:="<>J*"

Any suggestions?