Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 3 of 3

Thread: Filter and delete visible cells

  1. #1
    Join Date
    13th May 2011
    Posts
    2

    Filter and delete visible cells

    Hello,

    I am new to the site and I was hoping someone might understand an error I am getting. I know it has to do with the limits of specialcells in my search, but I'm not sure of the best way to fix it.

    Here is the code:

    VB:
    With Sheets("Scrap") 
        For h = 0 To Form_Fields.SDate_Combo.ListCount - 1 
            SRDate = Replace(Form_Fields.SDate_Combo.List(h), ".", "/") 
            ChkDate = Form_Fields.SDate_Combo.List(h) 
            If CDate(SRDate) >= CDate(SDate) And CDate(SRDate) <= CDate(EDate) Then 
                 'Do nothing
            Else 
                .Cells(1, 1).Select 
                Selection.AutoFilter 
                Selection.AutoFilter Field:=1, Criteria1:=ChkDate 
                [B] ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(1).Columns(1).Cells(2, 1).Select[/B] 
                range(Selection, Selection.End(xlToRight)).Select 
                range(Selection, Selection.End(xlDown)).Select 
                Selection.EntireRow.Delete 
                range("A1").Select 
                Selection.AutoFilter 
            End If 
        Next h 
    End With 
    
    
    the error says : runtime error 1004 excel cannot create or use the data range reference because it is too complex

    Any suggestions?

    Thanks..
    Last edited by AAE; May 13th, 2011 at 10:33. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Filter and delete visible cells

    Hello gwenky,

    Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:

    Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different


    Your title of "SpecialCells error 1004-" does not describe your thread or objective and is not helpful to those searching the forum for a solution to a similar need.

    Please note the change to your title, which is based on the objective stated in your thread.

    If the new title still does not accurately describe your thread you may make further edits as needed per the above guidelines.
    ------------------------------------------------------------------------

    VB:
    Option Explicit 
     
    Sub Set_PageBreaks() 
         
        Dim lastrow As Long, lastcolumn As Long, rngFilter As Range 
         
        Application.ScreenUpdating = False 
         
        With Sheets("Scrap") 
             
            lastrow = .Cells(Rows.Count, "A").End(xlUp).Row 
            lastcolumn = .Cells(1, Columns.Count).End(xlToLeft).Column 
             
            For h = 0 To Form_Fields.SDate_Combo.ListCount - 1 
                 
                SRDate = Replace(Form_Fields.SDate_Combo.List(h), ".", "/") 
                ChkDate = Form_Fields.SDate_Combo.List(h) 
                 
                If CDate(SRDate) >= CDate(SDate) And CDate(SRDate) <= CDate(EDate) Then 
                     'Do nothing
                Else 
                    Set rngFilter = .Range("A1", .Cells(lastrow, lastcolumn)) 
                    rngFilter.AutoFilter field:=1, Criteria1:="A-1-1-2" 
                    rngFilter.Offset(1, 0).SpecialCells(12).EntireRow.Delete 
                    .AutoFilterMode = False 
                End If 
            Next h 
        End With 
         
        Set rngFilter = Nothing 
         
        Application.ScreenUpdating = True 
         
    End Sub 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    13th May 2011
    Posts
    2

    Re: Filter and delete visible cells

    AAE--
    Thank you for making the correction. I will keep the title search in mind for future postings. I see the code you created. I am fairly new to editing VBA, it has become a task of mine at work. So from what I am able to tell my search results are too large for the specialcells to work accurately and the task needs split up? The code you posted, does that replace the code I posted? I added it and then called the sub, but I am still getting the same error so I'm inclined to think I'm not doing something right! When I debug the error it highlights this piece of code:

    VB:
    Function DR(Criteria As String, pos As Integer, IndexString As String) 
        Sheets(IndexString).Select 
        Selection.AutoFilter 
        Selection.AutoFilter field:=pos, Criteria1:=Criteria 
        ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Areas(1).Columns(1).Cells(2, 1).Select 
        Range(Selection, Selection.End(xlToRight)).Select 
        Range(Selection, Selection.End(xlDown)).Select 
        Selection.EntireRow.Delete 
        Range("A1").Select 
        Selection.AutoFilter 
    End Function 
    
    
    which comes from a Form. Any suggestions? Thanks, I hope this isn't too confusing I realize I am very new to this but I am trying to understand.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: June 25th, 2011, 06:01
  2. Replies: 14
    Last Post: January 2nd, 2011, 04:01
  3. Specialcells Property Error
    By lkrterp in forum EXCEL HELP
    Replies: 2
    Last Post: April 14th, 2007, 00:37
  4. Error 1004
    By nightkil in forum EXCEL HELP
    Replies: 7
    Last Post: February 22nd, 2007, 03:16

Bookmarks

Posting Permissions

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