VBA Selection.Delete Shift:=xlUp

cazdealer

Board Regular
Joined
Mar 12, 2011
Messages
96
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Try

Code:
Range(.Range("A1"), foundOne.Offset(-1, 0).Resize(, 7)).Delete shift:=xlShiftUp
 
Upvote 0

Forum statistics

Threads
1,224,514
Messages
6,179,223
Members
452,896
Latest member
IGT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top