CODEWITHSUNDEEP
excelvba
wezzer
wezzer

Reputation: 9

Delete blank rows

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).

results-image

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

Upvotes: 1

Views: 349

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

  • 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.

Upvotes: 2

Related Questions