Autofilter doesn't work past blank rows

M

Mark Graesser

Hi Joe
Maybe not the best solution, but you could type a space into the blank cells

Good Luck
Mark Graesse
[email protected]
Boston M

----- lunker55 wrote: ----

Autofilter doesn't filter anything past a blank row
Can this be overcome

Jo
 
L

lunker55

No good. I have a macro that creates alot of empty rows. I'll just create a
macro to delete blank rows, the filter. Thanks anyways.

joe
 
K

Ken Wright

No need to reinvent the wheel - Take your pick of the following, to quote but a
few:-


Sub DlBlnks()

On Error Resume Next ' In case there are no blanks
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97

'more information in
'Delete Cells/Rows in Range, based on empty cells, or cells with specific values
'http://www.mvps.org/dmcritchie/excel/delempty.htm
End Sub

===========================================
Public Sub DeleteBlankRows1b()
Dim rRow As Range
Dim rDelete As Range
For Each rRow In ActiveSheet.UsedRange.Rows
If Application.CountA(rRow) = 0 Then
If rDelete Is Nothing Then
Set rDelete = rRow
Else
Set rDelete = Union(rDelete, rRow)
End If
End If
Next rRow
If Not rDelete Is Nothing Then _
rDelete.EntireRow.Delete
End Sub

===========================================
Public Sub DeleteReallyBlankRows()
'Chip Pearson
'Will delete all rows that are entirely blank
Dim r As Long
Dim c As Range
Dim n As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For r = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
Rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

===========================================
Sub DeleteEmptyRows()
'John Walkenbach
'Will delete all rows that are entirely blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub
Sub DeleteEmptyRows2()
'John Walkenbach but Edited
'Will delete all rows where E:AI is entirely blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Cells(r, 5).Resize(1, 31)) = 0 Then Rows(r).Delete
Next r
End Sub

===========================================
Public Sub DeleteBlankRows():
'This will delete all the blank rows if cell in Col A is blank within the active
sheet.

On Error Resume Next
Intersect(ActiveSheet.UsedRange.EntireRow, Columns(1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
Public Sub DeleteSelectionBlanks():
'This will delete all the blank rows contained within a selection of blank rows.
'Select by dragging down on the row handles to select entire range containing
rows
'you wish to delete.

On Error Resume Next
Intersect(Selection.EntireRow, Columns(1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
 
D

Dave Peterson

If you select your whole range (including the blank rows) first, then apply
data|filter|autofilter, I think it'll work fine.

If your macro inserts rows after the filter has been applied and that damages
it, just remove the filter and reapply to the whole range--in the macro that
adds rows.
 
L

lunker55

Thanks Ken for the many options!

Joe

Ken Wright said:
No need to reinvent the wheel - Take your pick of the following, to quote but a
few:-


Sub DlBlnks()

On Error Resume Next ' In case there are no blanks
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97

'more information in
'Delete Cells/Rows in Range, based on empty cells, or cells with specific values
'http://www.mvps.org/dmcritchie/excel/delempty.htm
End Sub

===========================================
Public Sub DeleteBlankRows1b()
Dim rRow As Range
Dim rDelete As Range
For Each rRow In ActiveSheet.UsedRange.Rows
If Application.CountA(rRow) = 0 Then
If rDelete Is Nothing Then
Set rDelete = rRow
Else
Set rDelete = Union(rDelete, rRow)
End If
End If
Next rRow
If Not rDelete Is Nothing Then _
rDelete.EntireRow.Delete
End Sub

===========================================
Public Sub DeleteReallyBlankRows()
'Chip Pearson
'Will delete all rows that are entirely blank
Dim r As Long
Dim c As Range
Dim n As Long
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
n = 0
For r = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
Rng.Rows(r).EntireRow.Delete
n = n + 1
End If
Next r
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

===========================================
Sub DeleteEmptyRows()
'John Walkenbach
'Will delete all rows that are entirely blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
End Sub
Sub DeleteEmptyRows2()
'John Walkenbach but Edited
'Will delete all rows where E:AI is entirely blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Cells(r, 5).Resize(1, 31)) = 0 Then Rows(r).Delete
Next r
End Sub

===========================================
Public Sub DeleteBlankRows():
'This will delete all the blank rows if cell in Col A is blank within the active
sheet.

On Error Resume Next
Intersect(ActiveSheet.UsedRange.EntireRow, Columns(1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
Public Sub DeleteSelectionBlanks():
'This will delete all the blank rows contained within a selection of blank rows.
'Select by dragging down on the row handles to select entire range containing
rows
'you wish to delete.

On Error Resume Next
Intersect(Selection.EntireRow, Columns(1)).SpecialCells( _
xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub



--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------------------------- --
It's easier to beg forgiveness than ask permission :)
-------------------------------------------------------------------------- --
Click to expand...
 
Top