Re: Autofilter doesn't work past blank rows

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: lunker55 (this_is_not_my_email_address_at_hotmail.com)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 07:09:52 -0400

Thanks Ken for the many options!

Joe

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:OExV48iLEHA.3664@TK2MSFTNGP10.phx.gbl...
> 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
> Active***.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 Active***.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 = Active***.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 = Active***.UsedRange.Row - 1 + _
> Active***.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 = Active***.UsedRange.Row - 1 + _
> Active***.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
> ***.
>
> On Error Resume Next
> Intersect(Active***.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 :-)
> --------------------------------------------------------------------------
--
>
>
>
> "lunker55" <this_is_not_my_email_address@hotmail.com> wrote in message
> news:e716priLEHA.1192@TK2MSFTNGP11.phx.gbl...
> > 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
> >
> > "Mark Graesser" <anonymous@discussions.microsoft.com> wrote in message
> > news:FF53293D-3E5D-440B-B383-B2E52D849D99@microsoft.com...
> > > Hi Joe,
> > > Maybe not the best solution, but you could type a space into the blank
> > cells.
> > >
> > > Good Luck,
> > > Mark Graesser
> > > mark_graesser@yahoo.com
> > > Boston MA
> > >
> > >      ----- lunker55 wrote: -----
> > >
> > >      Autofilter doesn't filter anything past a blank row.
> > >      Can this be overcome?
> > >
> > >      Joe
> > >
> > >
> > >
> >
> >
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.668 / Virus Database: 430 - Release Date: 24/04/2004
>
>
Quantcast