Results 1 to 4 of 4

Thread: AutoFilter

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Excel 2003 user: Any way to Autofilter only, say, columns A through E, and leave F and G alone?
    Thanks in advance.
    P.S., as an alternative, is there a way to delete certain cells in certain columns where a cell in a respective meets or does not meet a certain criteria? For example, assume the area A2:F100 is highlighted. What I would like to do is delete the cells in columns A-F in the highlighted area when, say, the cell in column C of each row is either blank or contains a zero.
    Thanks again.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I am not sure what you mean by "leave alone". Filtering is done on rows so any rows hidden in A-E will use the same rows as F-IV so all will be affected.

    As to what I think you want:
    You can autofilter on the criteria in C to only display the cells to delete. you can select all the rows visible in A-E and press delete to clear them which will not affect cols F/G. (you can not delete the rows without deleting the rows in F/F though)

    If you need the rows deleted in A-E, and after clearing them, IF they are are the only blank cells in your sheet you can
    Edit - goto
    [special]
    Select "blanks"
    then right-click
    delete
    shift cells up
    to delete the rows in A-E

    If you have blanks in F or G or in other parts of A-E this will delete them as well and a macro may be a better alternative to just delete the cells in A-E (and move the others up) which have a blank or zero in Col C.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='jlkirk' post='773050' date='30-Apr-2009 09:54']Excel 2003 user: Any way to Autofilter only, say, columns A through E, and leave F and G alone?
    Thanks in advance.
    P.S., as an alternative, is there a way to delete certain cells in certain columns where a cell in a respective meets or does not meet a certain criteria? For example, assume the area A2:F100 is highlighted. What I would like to do is delete the cells in columns A-F in the highlighted area when, say, the cell in column C of each row is either blank or contains a zero.
    Thanks again.[/quote]

    Using AutoFilter only you cannot do what you want. Any data to the right or left of an Autofilter will be impacted by an Autofilter. Classic work arounds include 1. Copy your A - E data to a new blank Worksheet and filter the data there
    2. Move the data in F and G to a different palce, either above or below the other data.
    3. Use Advance Filter to Extract the data you want and have the extract in a different place on the worksheet.
    4. Consider a Pivot Table based only on the data in Cole A-E.

    As to your other question, your best bet is VBA.
    Sub RowCleanUp ()
    Dim rng as Range
    Dim rngMatch as Range
    Dim CRows as Integer
    Dim r as Integer
    Set rng = Selection
    Set rngMatch = Nothing
    CRows = rng.Rows.Count
    For r = 1 To CRows

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    [quote name='duthiet' post='773108' date='30-Apr-2009 12:36']Using AutoFilter only you cannot do what you want. Any data to the right or left of an Autofilter will be impacted by an Autofilter. Classic work arounds include 1. Copy your A - E data to a new blank Worksheet and filter the data there
    2. Move the data in F and G to a different palce, either above or below the other data.
    3. Use Advance Filter to Extract the data you want and have the extract in a different place on the worksheet.
    4. Consider a Pivot Table based only on the data in Cole A-E.

    As to your other question, your best bet is VBA.
    Sub RowCleanUp ()
    Dim rng as Range
    Dim rngMatch as Range
    Dim CRows as Integer
    Dim r as Integer
    Set rng = Selection
    Set rngMatch = Nothing
    CRows = rng.Rows.Count
    For r = 1 To CRows[/quote]


    above VBA did not finish

    Here is full VBA

    Sub RowCleanUp()
    Dim rng As Range
    Dim RngMatch As Range
    Dim r As Integer
    Dim CRows As Integer
    Set rng = Selection
    Set RngMatch = Nothing
    CRows = rng.Rows.Count
    For r = 1 To CRows
    If rng.Cells(r, 1).Value = 0 Then
    If RngMatch Is Nothing Then
    Set RngMatch = rng.Cells(r, 1)
    Else
    Set RngMatch = Application.Union(RngMatch, rng.Cells(r, 1))
    End If
    End If
    Next r
    RngMatch.Select
    RngMatch.EntireRow.Delete
    End Sub

    You would need to modify the above to fit your facts.
    It could be changed to loop through more than one Column for data
    It could be changed to select the item and the cell or cells to th right or left
    It could also be modified to only delete the data in the cells rather than the delete the entire row


    Hope this can help.

    Tom Duthie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •