Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
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 "Karin" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
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 -- Gary''s Student - gsnu200838 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
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 "Sheeloo" wrote: 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 "Karin" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
It does not seem to be working - does it need to select the 4 cells before
the Delete? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200838 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
It seems to be deleting 4 rows down, not the 4 cells to the right.
"Sheeloo" wrote: 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 "Karin" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
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 "Karin" wrote: It seems to be deleting 4 rows down, not the 4 cells to the right. "Sheeloo" wrote: 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 "Karin" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
My error.
Replace the line: s="Grand Total" with: s="Grand Total:" -- Gary''s Student - gsnu200839 "Karin" wrote: It does not seem to be working - does it need to select the 4 cells before the Delete? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200838 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to remove cells (not absolute)
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. "Gary''s Student" wrote: My error. Replace the line: s="Grand Total" with: s="Grand Total:" -- Gary''s Student - gsnu200839 "Karin" wrote: It does not seem to be working - does it need to select the 4 cells before the Delete? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200838 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
absolute cell reference macro | Excel Discussion (Misc queries) | |||
Pivot Macro - select absolute value | Excel Discussion (Misc queries) | |||
How to chnge 35 relative cells to 35 absolute cells at one time. | Excel Worksheet Functions | |||
Help with absolute references in a macro | Excel Discussion (Misc queries) | |||
Macro or Formula to remove Text from Cells | Excel Worksheet Functions |