Excel code using autofilter to delete blank rows? 1

[AppStaff]

Does anyone have code they can post for Excel that uses the autofilter command to delete any blank rows in a worksheet?

Thanks for any responses

 

[TonyJollans]

Hi AppStaff,

If you record yourself doing it (Tools > Macros > Record New Macro) you will get code which works.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[AnotherHiggins]

Why do you need to use autofilter? There are many ways to do this.

Is there a certain column that will always be empty on rows that you want to delete and never be empty on rows you want to keep?

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AppStaff]

Tony, I tried that and couldn't get it to work. I develop primarily in access and there are plenty of references I have yet to learn in excel.

John, Yes that condition applies. I'm really open to any method. It should be a simple thing to do but being inexperienced in excel I'm struggling. I posted my attempt in another post and someone said the easiest method would be to use autofilter. I tried to get this to work but wasn't successful. Any working code is appreciated

 

[TonyJollans]

Hi AppStaff,

What didn't work? You shouldn't need to worry about references to do as you asked. Can you post what you did record?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[AnotherHiggins]

This will work:

Sub DeleteEmptyRows()
'select column A
columns("A:A").Select
'select any blank cells in column A
Selection.SpecialCells(xlCellTypeBlanks).Select
'select the entire row for each of the blank cells in column A
Selection.EntireRow.Select
'delete selection
Selection.Delete
end sub

All of that can be combined into a single line of code:

Sub DeleteEmptyRows()
columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

If you ever need to delete only rows where the entire row is blank, you can use the following (it also deletes all columns where the entire column is empty):

Sub z_DeleteEmptyRowsAndColumns()
Application.ScreenUpdating = False
'following grabs the current activecell
BegAddress = ActiveCell.Address

Range("a1").EntireRow.Select
    With Selection
        Do
            If Application.WorksheetFunction.CountA(Selection) = 0 Then
            Selection.EntireRow.Delete
            Else
            Selection.Offset(1, 0).Select
            End If
        RowCount = ActiveSheet.UsedRange.Rows.Count
        Loop Until ActiveCell.Row >= RowCount
    End With

Range("a1").EntireColumn.Select
    With Selection
        Do
            If Application.WorksheetFunction.CountA(Selection) = 0 Then
            Selection.EntireColumn.Delete
            Else
            Selection.Offset(0, 1).Select
            End If
        ColumnCount = ActiveSheet.UsedRange.columns.Count
        Loop Until ActiveCell.Column >= ColumnCount
    End With

Range(BegAddress).Activate
Application.ScreenUpdating = True
End Sub

I use that one pretty often on data that is imported from other sources. NOTE:If you are dealing with several thousand rows of data, that last macro can take several seconds to run.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[AppStaff]

Here is the code I was trying to get to work. I was having errors on the activecell reference. I'm not sure how that works exactly but I saw it in other help samples.

John, Thanks much for your code. Clearly i underestimated the task I'll see if I can get that to work. Star for your effort.

Dim RowStart As Range

Dim RowFinish As Range

Worksheets("sheet1").Activate

Set RowStart = Cells("A11")
Set RowFinish = Cells("A500")
RowStart.Select
Do While ActiveCell < Cells(RowFinish)
If ActiveCell = "" Then
Rows(ActiveCell).Select
Selection.Delete Shift:=xlUp
ActiveCell.Move
End If
Loop

 

[AnotherHiggins]

Clearly i underestimated the task....

Not at all! You said that your data does meet the criteria that there is a certain column that will always be empty on rows that you want to delete and never be empty on rows you want to keep.

That means all you have to use is
[COLOR=blue white]columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete[/color]

That's it! No looping, no fuss. Just change the [COLOR=blue white]"A:A"[/color] for whatever column will always be enpty on the rows you want to delete. I just threw in the last (long) macro as an FYI.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886