macro to remove cells (not absolute)

  • Thread starter Thread starter Karin
  • Start date Start date
K

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!
 
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
 
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
 
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
 
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
 
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.
 
Back
Top