Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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:

    Code:
    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
                    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 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 11:33. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


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

    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.
    ------------------------------------------------------------------------

    Code:
    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:

    Code:
    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, 07:01
  2. Replies: 14
    Last Post: January 2nd, 2011, 05:01
  3. Specialcells Property Error
    By lkrterp in forum EXCEL HELP
    Replies: 2
    Last Post: April 14th, 2007, 01:37
  4. Error 1004
    By nightkil in forum EXCEL HELP
    Replies: 7
    Last Post: February 22nd, 2007, 04: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