Results 1 to 3 of 3

VBA Selection.Delete Shift:=xlUp

This is a discussion on VBA Selection.Delete Shift:=xlUp within the Excel Questions forums, part of the Question Forums category; Code: Dim foundOne As Range Dim foundTwo As Range On Error Resume Next With Sheets("data") Set foundOne = .Range("B2:B2501").Find(What:="9:30:00 AM", ...

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    96

    Default VBA Selection.Delete Shift:=xlUp

    Code:
    Dim foundOne As Range
    Dim foundTwo As Range
    On Error Resume Next
    With Sheets("data")
        Set foundOne = .Range("B2:B2501").Find(What:="9:30:00 AM", After:=.Range("b2"), LookIn:=xlFormulas, _
                                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, MatchCase:=False)
        If foundOne.Row > 1 Then
            Range(.Range("B1"), foundOne.Offset(-1, 0)).EntireRow.Delete Shift:=xlUp
        End If
        Set foundTwo = .Range("B2:B0001").Find(What:="fini", After:=.Range("b2"), LookIn:=xlFormulas, _
                                    LookAt:=xlPart, SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, MatchCase:=False)
        Range(foundTwo.Offset(1, 0), .Cells.SpecialCells(xlCellTypeLastCell)).Delete Shift:=xlUp
    End With
    On Error GoTo 0
    Hi, the first part of this code above finds for me the first time ""9:30:00 AM" appears in column B and delete all rows above.

    How could I change this part to delete only cell A to G from those rows and shift up... Not deleting the full row.

    I guess I need to change
    Range(.Range("B1"), foundOne.Offset(-1, 0)).EntireRow.Delete

    for something that select the rows above (from A to G) and do this:
    Selection.Delete Shift:=xlUp


    thanks

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,514

    Default Re: VBA Selection.Delete Shift:=xlUp

    Try

    Code:
    Range(.Range("A1"), foundOne.Offset(-1, 0).Resize(, 7)).Delete shift:=xlShiftUp
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Mar 2011
    Posts
    96

    Default Re: VBA Selection.Delete Shift:=xlUp

    great!

    thanks

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com