[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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 ;)
0
gisvpn
Asked:
gisvpn
  • 4
  • 3
  • 2
1 Solution
 
SteveCommented:
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.
0
 
gisvpnAuthor Commented:
Unfortunatley not I would like to do this in VBA ;)
0
 
SteveCommented:
To speed up the code...

Define the two DateValues only once rather than refering each time:

Dim StartDate as date
dim EndDate as date

StartDate = Sheets("Summary").Range("H7").Value
EndDate = Sheets("Summary").Range("J7").Value

If .Cells(Row, "C").Value < StartDate Or .Cells(Row, "C").Value > EndDate Then
                .Rows(Row).Delete

Open in new window

this will stop the code looking for the values of from and to each time.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SteveCommented:
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

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

you can do the autofilter approach with VBA!

cheers, teylyn
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
0
 
SteveCommented:
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;"
0
 
gisvpnAuthor Commented:
Hi teylyn,

I cannot seem to get the filter version to work. It completes but the right rows have not been deleted.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Maybe I have the filter dates backwards. Maybe you need

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now