Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can Relative Cells/Ranges be Cleared when Rows are Inserted?
The code below copies 2 adjacent rows and inserts them either above or
below the 2 current rows (2 rows = 1 record). However, I need to clear certain parts of the 2 newly inserted rows: --contents of cols. D and E (absolute), --contents of cols. AP to the end of all consecutive cols. with the text "Activity" in row 12 (the ending col. is relative since the number of activities will increase). "Activity" is inserted into each of these cols. as labels in row 12. --and the contents of all consectutive cols. with dates 'mm/dd/yy' typed in row 12 (the starting col. is relative since new 'Activity' cols. will be inserted through time). The ending col. can just be IV. I've been playing around with range().clear, but cannot figure out how to clear these separate ranges on the 2 separate rows. ================= 'Great code from Bernie Deitrick Sub InsertBlockAboveOrBelow() Dim myCell As Range Dim newFName As String Dim newLName As String newFName = InputBox("What is the new first name?") newLName = InputBox("What is the new last name?") If Cells(ActiveCell.Row, 1).Value = "" Then Set myCell = Cells(ActiveCell.Row, 1).End(xlUp) Else Set myCell = Cells(ActiveCell.Row, 1) End If With myCell.Resize(2).EntireRow If MsgBox("Above = ""Yes"", Below = ""No""", vbYesNo) = vbYes Then .Copy .Insert myCell.Offset(-2).Value = newFName myCell.Offset(-2,1).Value = newLName Else .Copy .Offset(4).Insert myCell.Offset(2).Value = newFName myCell.Offset(2,1).Value = newLName End If End With Application.CutCopyMode = False End Sub Thanks, Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows and keep merged cells in the inserted row | Excel Discussion (Misc queries) | |||
Change colour of cells when content is altered/changed BUT NOT TO INSERTED OR DELETED ROWS | Excel Discussion (Misc queries) | |||
Relative Ranges - Multiple Cells | Excel Programming | |||
Inserting/Deleting Rows when values are added /cleared | Excel Programming | |||
select ranges of cells relative to location and then paste | Excel Programming |