Announcement

Collapse
No announcement yet.

Filter and delete visible cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 10:33. Reason: revise thread title

  • #2
    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

    Comment


    • #3


      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.

      Comment

      Working...
      X