VBA: Code review - Field delete issue

Question

The code in fact help me to delete any unwanted blank fields in my Sheet, it was working fine till yesterday but today when my workesheet had more than 2300 lines of data the code delete first 2000 rows from the sheet and gave me remaining 300 Rows.

I know the problem is in this line

 Range ("A2:K2000".Select)

Is there any solution to replace this scenarios other than changing the value of K

Code

Sub Delete_blank()

With ActiveSheet
    If .Autofiltermode = False Then Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range ("$A$2:$C$") & lastrow.AutoFilter Field:=1, Criterial:="="
    ActiveSheet.Range ("$A$2:$C$") & lastrow.AutoFilter Field:=2, Criterial:="="
    ActiveSheet.Range ("$A$2:$C$") & lastrow.AutoFilter Field:=3, Criterial:="="

    Range ("A2:K2000").Select
    Selection.EntireRow.Delete
    ActiveSheet.ShowAllData
    .AutoFilterMode = False
End With
End Sub

'INPUT       : Sheet, the worksheet we'll search to find the last row
'OUTPUT      : Long, the last occupied row
'SPECIAL CASE: if Sheet is empty, return 1
Public Function LastOccupiedRowNum(Sheet As Worksheet) As Long
Dim lng As Long
If Application.WorksheetFunction.CountA(Sheet.Cells) <> 0 Then
    With Sheet
        lng = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
    End With
Else
    lng = 1
End If
LastOccupiedRowNum = lng
End Function

I used the record macro function to create this code and now I understood the problem with it.

Please help me to get a solution

Thanks in advance.

You already have code to find the last row on the output sheet, why not use something similar to find the last row on the input sheet? — Scott Craner, Dec 15 '17 at 15:00
Also `If .Autofiltermode = False Then Cells.Select` is on one line, therefore the lines after that will trigger no matter what. Is that what you intended? Or is that whole block supposed to be an `If` statement? Also where/how is `lastrow` declared? — BruceWayne, Dec 15 '17 at 15:03
There was a slight correction in the code, i just updated it - @Scott Craner i tried the same but it will not work. When there is any blank rows in this sheet the lastrow wont recognize the same because there is no value available on those blank fields — mithun nair, Dec 15 '17 at 16:14
You can work with the `UsedRange` to find the last row. This works regardless of the cells containing values or not. — Fernando J. Rivera, Dec 15 '17 at 17:24

score 1 · Answer 1 · answered Dec 15 '17 at 17:15

1

How about replacing your line of code:

Range ("A2:K2000".Select)

With something like:

 ActiveSheet.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select

answered Dec 15 '17 at 17:15

Xabier

7,587
1
8
20

`ActiveSheet.Range("A2", cells.SpecialCells(xlCellTypeLastCell))..EntireRow.Delete`, you mean. The Special cell reference was a little off, and you don't actually need to select the cell to delete it, but yeah this is what I would do. – Fernando J. Rivera Dec 15 '17 at 17:29
I've actually tested the above and it works as expected for me, but yeah thanks for the pointer, will check it out for sure... :) – Xabier Dec 15 '17 at 17:30
Thanks @ Fernando J. Rivera, it worked if there is any blank field, but it will delete rows with data if there is no blank fields... any solution for that ? – mithun nair Dec 16 '17 at 03:41

VBA: Code review - Field delete issue

1 Answers1