Delete blank rows

Question

I'd like to ask some help about my code below. This is captured using the macro recorder after importing the file. Basically, I imported the a text file and after importing the file to the Excel (macro begins) I delete the rows 10-12 and auto filter it which is works. (macro stop)

After that, I want to delete all the empty/blank rows from A10-onwards. So I added the code (under "'ADDED to DELETE ROWS"). What I need is to delete the all the rows with "TEST", delete all the blank rows and filter the first rows.

The first part of the code is working, but to delete the blank rows didn't work. Can you please help me to identify what wrong with my code below. Please click the link to show result (before and after).

Thank you and appreciate your help.

Sub Macro2()
    Rows("10:12").Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter

    'ADDED to DELETE ROWS
    'Worksheets("Sheet1").Activate
    On Error Resume Next
    With Range("A10:A")
        .Value = .Value
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

Regards,wezzer

Range("A10:A") won't work to select the rest of the column under cell A10. Look here for alternatives: https://stackoverflow.com/questions/6301665/how-to-count-the-number-of-rows-in-excel-with-data/14390619#14390619 — xthestreams, May 30 '17 at 02:07
thanks xthestreams, I'm new to excel-macro. that code is from other post and I edited it. Basically, I want to delete the blank rows and as well as the rows with text like "text". — wezzer, May 30 '17 at 03:53

score 2 · Answer 1 · edited Sep 21 '17 at 15:07

I strongly recommend to avoid using On Error Resume Next as this only mutes error messages and you don't see what's happening.
Always qualify a Range with a worksheet like myWs.Range to avoid selecting the wrong sheet and mess up data. Never assume the worksheet.
Avoid using Select. Selecting slows down your code a lot.

Finally your code could look something like that:

Sub DeleteSeveralRows()
    Dim myWs As Worksheet
    Set myWs = ThisWorkbook.Worksheets("MySheetName") 'set your sheet name

    'Delete rows 10 to 12 and autofilter
    With myWs.Rows("10:12")
        .Delete Shift:=xlUp
        .AutoFilter
    End With

    Dim lastRow As Long
    lastRow = myWs.Range("A" & myWs.Rows.Count).End(xlUp).Row 'find last used row

    With myWs.Range(myWs.Cells(10, "A"), myWs.Cells(lastRow, "A"))
        .Value = .Value  'convert formulas to values whithin the range from with block (column A only)
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete 'delete rows where column A is blank
    End With
End Sub

As an alternative to .Value = .Value which converts only formulas in column A into values (only column A is referenced in the with-block). You can use .EntireRow.Value = .EntireRow.Value to convert the entire row instead.

I also recommend to use myWs.UsedRange.Autofilter as last statement instead of filtering in the beginning, to ensure all rows are used for autofilter.

@wezzer If this answer solved your issue please consider marking it as solution so anyone can see that this question is solved. — Pᴇʜ, Jun 01 '17 at 05:57

Delete blank rows

1 Answers1

Linked