Filter out blank rows in a table with VBA

[Mercy]

I have a workbook that is being used by novice excel users. There are three sheets where they enter information from three different sources to be compared. The columns are always the same but the number of rows is never the same. In a hidden sheet I have created links to combine all the information they have entered. I have another sheet that takes that combined information and makes it pretty and sorts it for their comparison. Because I never know how many rows of information will be entered there are several blank rows that I would like to filter out of the comparison table.

I am looking for code that will activate the autofilter to deselect "(blank)" and "(Blanks)" as excel has chosen to label these items.

 

[bugmonsta]

This will autofilter with NO blanks, in my test I just filtered columns A to F:

Sub Macro1()
'
' Macro1 Macro

    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$15").AutoFilter Field:=1, Criteria1:="<>"
    Range("A1").Select
End Sub

However these rows will always be there (and that annoys me! ) so you could always delete blanks rows, with TrevorG example as shown below:

Sub usedR()
' credits to TrevorG @ MrExcel
'- http://www.mrexcel.com/forum/excel-questions/603354-visual-basic-applications-delete-blank-rows.html
 ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
 Selection.Rows(i).EntireRow.Delete
End If
Next i
 .Calculation = xlCalculationAutomatic
 .ScreenUpdating = True
End With
End Sub

 

[Mercy]

Rather than select, I ended up just using this:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="<>"

Thanks for pointing me in the right direction.

I don't mind the extra rows still being there since this is just a temporary tool for comparison.

This will autofilter with NO blanks, in my test I just filtered columns A to F:

Sub Macro1()
'
' Macro1 Macro

    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$F$15").AutoFilter Field:=1, Criteria1:="<>"
    Range("A1").Select
End Sub

However these rows will always be there (and that annoys me! ) so you could always delete blanks rows, with TrevorG example as shown below:

Sub usedR()
' credits to TrevorG @ MrExcel
'- http://www.mrexcel.com/forum/excel-questions/603354-visual-basic-applications-delete-blank-rows.html
 ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
 .Calculation = xlCalculationManual
 .ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
 Selection.Rows(i).EntireRow.Delete
End If
Next i
 .Calculation = xlCalculationAutomatic
 .ScreenUpdating = True
End With
End Sub