macro to remove cells (not absolute)

[Karin]

Hi, I want to find Grand Total: and then delete the 4 cells after it so that
the 4 cells underneath move up. The rows and columns are not absolute (will
be different every time I run the macro).

How do I code for this?

TIA!

 

[Sheeloo]

Try
Sub deleteCells()
Range("A1").Select
Cells.Find(What:="Grand Total:", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(4, 0)).Delete
End Sub

 

[Gary''s Student]

Sub Karin()
Dim s As String, r As Range
s = "Grand Total"
For Each r In ActiveSheet.UsedRange
If r.Value = s Then
Range(r.Offset(1, 0), r.Offset(4, 0)).Delete Shift:=xlUp
Exit Sub
End If
Next
End Sub

 

[Sheeloo]

Sorry, missed adding Shift:=xlUp

Use this one...

Sub Macro1()
Range("A1").Select
Cells.Find(What:="Grand Total:", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(4, 0)).Delete Shift:=xlUp
End Sub

 

[Karin]

It does not seem to be working - does it need to select the 4 cells before
the Delete?

 

[Karin]

It seems to be deleting 4 rows down, not the 4 cells to the right.

 

[Sheeloo]

In that case use...
(It will delete the first four cells to the right of the cell containing
Grand Total:

You can play around with the number in OFFSET..
Offset(0, 1) means 0 row down and 1 column to right...

Sub Macro1()
Range("A1").Select
Cells.Find(What:="Grand Total:", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Delete Shift:=xlUp
End Sub

 

[Gary''s Student]

My error.

Replace the line:
s="Grand Total"
with:
s="Grand Total:"

 

[Karin]

Thank you - I had fixed that. I think you are going down 4 rows and
deleting rather than going over 4 cells - also I am on the grand total line,
so I need to delete the 4 cells on that line so that the 4 cells on the line
below come up.

Original:
Range(r.Offset(1, 0), r.Offset(4, 0)).Delete Shift:=xlUp

Modified:
r.Offset(0, 2)).Delete Shift:=xlUp
r.Offset(0, 3)).Delete Shift:=xlUp
r.Offset(0, 4)).Delete Shift:=xlUp

This is working. Not sure if I can combine the 3.
Thank you very much for your assistance.