VBA Delete row if

Question

All i want to do is to optimize my current delete row code. At this stage this step take to much time.

        Dim miesiac2 As Integer '--->current month
        miesiac2 = Range("b1").Value
        Dim LastRow As Long
        LastRow = [A65536].End(xlUp).Row
        For i = LastRow To 1 Step -1
        If Cells(i, 1) = miesiac2 Then Rows(i & ":" & i).EntireRow.Delete
        Next i

So... If column A equals current month then EntireRow.Delete Any idea?

(a) You never assign a value to miesiac2. (b) It won't be noticeable, but you will get a small improvement by changing Rows(i & ":" & i) to Rows(i). (c) Have you set Application.Calculation to xlCalculationManual? And Application.ScreenUpdating to False? — YowE3K
– YowE3K, Commented Jun 12, 2017 at 8:29
(a) don' worry about that, just add it to my 'question (b) i'll check it. Thx (c) yeap — Maciek Chilu
– Maciek Chilu, Commented Jun 12, 2017 at 8:32
The best way is to save the rows with miesiac2 as a specific range and then delete the range only once. — Vityata
– Vityata, Commented Jun 12, 2017 at 8:39
Have you checked your locals to see that miesiac2 is being populated by the correct data — Lowpar
– Lowpar, Commented Jun 12, 2017 at 8:39

Vityata · Accepted Answer · 2017-06-12 12:48:36Z

1

That's something I have built so far:

Option Explicit

Public Sub TestMe()

    Application.ScreenUpdating = False

    Dim miesiac2        As Long
    Dim LastRow         As Long
    Dim i               As Long
    Dim rRange          As Range

    miesiac2 = Range("b1").Value
    LastRow = [A65536].End(xlUp).Row 'xl2003

    For i = LastRow To 1 Step -1
        If Cells(i, 1) = miesiac2 Then
            If rRange Is Nothing Then
                Set rRange = Rows(i)
            Else
                Set rRange = Union(rRange, Rows(i))
            End If
        End If
    Next i

    If Not rRange Is Nothing Then rRange.Select

    Application.ScreenUpdating = True
End Sub

It uses a Union and it selects the rows instead of deleting them. Its for visibility reasons, but you can fix it. Furthermore, the 65K rows are only in Excel 2003, in later versions the rows are 1Mln+. Last but not least - do not use integer in Excel, its slow and dangerous.

edited Jun 12, 2017 at 12:48

answered Jun 12, 2017 at 9:02

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Maciek Chilu Over a year ago

Super, thx. One change: If Not (rRange Is Nothing) Then rRange.Select

Maciek Chilu Over a year ago

One more error here... When current mounth do not exist then this code is selecting all date... not good

Vityata Over a year ago

@MaciekChilu - strange. I have just checked it on an empty sheet with 3-4 values in the first column and it was working quite ok - not selecting anything.

Maciek Chilu Over a year ago

dont need to worry about. Just add IF statement and its fine ^^

PankajKushwaha · Accepted Answer · 2017-06-12 16:06:46Z

0

This is what I could cook up in hurry

Sub delete_on_condition()
    Dim wb_export As Workbook
    Dim wb_export_sheet As Worksheet
    Dim arr_raw_dump As Variant
    Dim arr_final
    Dim findcell As Range

    Set wb_export = ThisWorkbook ' CHANGE IT IF REQURIED
    Set wb_export_sheet = wb_export.Sheets(1)    'CHANGE IT IF REQUIRED

    Dim ctr As Long
    ctr = 0
    With wb_export_sheet.Range("A1").CurrentRegion ' OR With wb_export_sheet.USEDRANGE

    Do
         Set findcell = .Find("SOME TEXT")
            If ctr = 0 And findcell Is Nothing Then
                MsgBox "No data found"
                Exit Sub
            End If

         wb_export_sheet.Rows(findcell.Row).Delete
         Set findcell = .Find("SOMETEXT")
         ctr = ctr + 1
    Loop While Not findcell Is Nothing
    End With
End Sub

answered Jun 12, 2017 at 16:06

PankajKushwaha

9682 gold badges12 silver badges28 bronze badges

1 Comment

Maciek Chilu Over a year ago

thx, but problem alredy has been solved . Vityata solution is working really fast. It speed up my App. from 12 to 2 minuts

Collectives™ on Stack Overflow

VBA Delete row if

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related