asked on

A quicker way to delete rows?

Hello,

I am using the code below to remove all rows that dont fit within a time window - it takes ages to run!

Is there another, quicker way to do this?

I just want to delete all rows which dont fall within the date range defined here :

Start Date: Sheets("Summary").Range("H7").Value
End Date: Sheets("Summary").Range("J7").Value

----

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")

ws.Activate

Dim Row As Long

With ThisWorkbook.Sheets("Data")
For Row = .UsedRange.Row + .UsedRange.Rows.Count - 1 To .UsedRange.Row Step -1

'MsgBox .Cells(Row, "T").Value
'MsgBox .Cells(Row, "C").Value, vbInformation
'MsgBox Sheets("Summary").Range("H7").Value, vbCritical

If .Cells(Row, "C").Value < Sheets("Summary").Range("H7").Value Or .Cells(Row, "C").Value > Sheets("Summary").Range("J7").Value Then
.Rows(Row).Delete
End If
Next Row
End With

Please Help ;)

Steve

if you wish to do this outside of code...

Add an autofilter
Filter between the two dates to show the rows to delete.
highlight all the rows that are shown
press [ctrl]+[-] to delete all those rows.

gisvpn

ASKER

Unfortunatley not I would like to do this in VBA ;)

ASKER CERTIFIED SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve

also at the start turn of calculation
and turn it back on after

Application.calculation = xlManual
'... code ...
Application.calculation = xlCalculationAutomatic

Open in new window

I am guessing you have screenupdating off too...

Application.screenupdating = false

Open in new window

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

you can do the autofilter approach with VBA!

cheers, teylyn

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Something along these lines:

Sub DeleteRows()

Dim lRow As Long
Dim FromDate As Date
Dim ToDate As Date
Dim lStartDate As Long
Dim lEndDate As Long

FromDate = Sheets("Summary").Range("H7")
ToDate = Sheets("Summary").Range("J7")

FromDate = DateSerial(Year(FromDate), Month(FromDate), Day(FromDate))
ToDate = DateSerial(Year(ToDate), Month(ToDate), Day(ToDate))

lStartDate = FromDate
lEndDate = ToDate


    Sheets("Data").UsedRange.AutoFilter
    Sheets("Data").UsedRange.AutoFilter Field:=2, Criteria1:= _
        ">=" & lStartDate, Operator:=xlAnd, Criteria2:="<=" & lEndDate
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    ActiveSheet.UsedRange.AutoFilter Field:=2
    Range("A1").Select
    Selection.AutoFilter
End Sub

Open in new window

cheers, teylyn

Steve

I was also thinking... you coulkd always use ADO and do an SQL Select query to get the data you want. Would allow for the use of:
"SELECT * FROM [DATA] WHERE lDate Between lStart And lEnd;"

gisvpn

ASKER

Hi teylyn,

I cannot seem to get the filter version to work. It completes but the right rows have not been deleted.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Maybe I have the filter dates backwards. Maybe you need

Sheets("Data").UsedRange.AutoFilter Field:=2, Criteria1:= _
"<=" & lStartDate, Operator:=xlAnd, Criteria2:=">=" & lEndDate