I have an excel sheet having more than 30000 rows.
I want to delete entire rows if value in column B is either blank or zero or "Min".
The first row contains headers.
The sample excel file is attached herewith.
I have an excel sheet having more than 30000 rows.
I want to delete entire rows if value in column B is either blank or zero or "Min".
The first row contains headers.
The sample excel file is attached herewith.
Hi, try
![]()
Sub Marreco_1059651() Dim startrow As Long 'starting row number here startrow = 2 ' Assuming data to check is in A Column Do Until startrow > Cells(Cells.Rows.Count, "A").End(xlUp).Row If Cells(startrow, 2).Value = "Min" Or Cells(startrow, 2).Value = 0 Or Cells(startrow, 2).Value = "" Then Rows(startrow).Delete Else startrow = startrow + 1 End If Loop End Sub
"No xadrez nem sempre a menor dist?ncia entre dois pontos ? uma linha reta" G. Kasparov.
If your problem is solved, please say so clearly, and mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select b from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.
The macro works like a charm.
thanks a lot.
Thank you boss. you saved lot of my work.
You can use a filter
Or a reverse loop![]()
Sub Macro1() Dim Rws As Long, Rng As Range, fRng As Range Rws = Cells(Rows.Count, "B").End(xlUp).Row Set fRng = Range(Cells(1, 1), Cells(Rws, 4)) Set Rng = Range(Cells(2, 1), Cells(Rws, 1)) Application.ScreenUpdating = False fRng.AutoFilter Field:=2, Criteria1:=Array( _ "0", "Min", "="), Operator:=xlFilterValues Rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete ActiveSheet.AutoFilterMode = 0 End Sub
![]()
Sub DeleteRowsReverseLoop() Dim lastrow As Long, r As Long Application.ScreenUpdating = False lastrow = Cells(Rows.Count, "B").End(xlUp).Row For r = lastrow To 1 Step -1 If Cells(r, 2).Value = "0" Or Cells(r, 2).Value = "Min" Or Cells(r, 2).Value = "" Then Rows(r).Delete End If Next r End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks