CODEWITHSUNDEEP
vbaexceldelete-row
Maciek Chilu
Maciek Chilu

Reputation: 15

VBA Delete row if

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?

Upvotes: 1

Views: 802

Answers (2)

PankajKushwaha
PankajKushwaha

Reputation: 928

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

Upvotes: 0

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 1

Related Questions