Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    xlCellTypeVisible (2k)

    I have a db with 12 fields. I want to delete one record with vb.

    <pre>With Range("GL_Database")
    .AutoFilter
    'FILTER FOR DATE
    .AutoFilter Field:=3, Criteria1:=strDate
    'FILTER FOR TENANT
    .AutoFilter Field:=4, Criteria1:=strTenID
    'IF MORE THAN ONE RECORD IS STILL VISBLE
    If .SpecialCells(xlCellTypeVisible).Count > 24 Then
    .AutoFilter Field:=10, Criteria1:=strCkNum
    End if

    'NOW THAT WE HAVE ONLY ONE RECORD VISIBLE
    'DELETE THE RECORD
    .SpecialCells(xlCellTypeVisible). <font color=blue>Range("A2").EntireRow</font color=blue> .Delete
    End With</pre>


    The last line above ignores the "Visible Cells" and deletes the 1st record of the db - the field names
    being row 1 of the db.

    I also tried <font color=blue>.rows(2).delete </font color=blue> -no luck

    Any suggestions?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xlCellTypeVisible (2k)

    It is kind of hard to know exactly without a copy of your workbook, but, as a guess, you might need to replace the line that deletes the row with something like this:

    <pre>Public Sub test()
    If .SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
    .SpecialCells(xlCellTypeVisible).Areas(.SpecialCel ls(xlCellTypeVisible).Areas.Count).Range("A1").Ent ireRow .Delete
    Else
    .SpecialCells(xlCellTypeVisible).Range("A2").Entir eRow.Delete
    End If
    End Sub
    </pre>

    Legare Coleman

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

    Re: xlCellTypeVisible (2k)

    If I understand correctly you want to delete the rows that are filtered but not the "header row". Since the header row is also "visible" you must "remove this from the deletion:
    Something like this should work:

    <pre>Intersect(Rows("2:65536"), .SpecialCells(xlCellTypeVisible)).EntireRow.Delete </pre>


    Steve

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: xlCellTypeVisible (2k)

    Thanks,

    I forgot about the Areas property.

    .SpecialCells(xlCellTypeVisible).Areas(2).Delete

    did the job I wanted.

Posting Permissions

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