PC Review


Reply
Thread Tools Rate Thread

How can Relative Cells/Ranges be Cleared when Rows are Inserted?

 
 
Arnold
Guest
Posts: n/a
 
      6th Aug 2007
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 With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert rows and keep merged cells in the inserted row Buggelsgaard Microsoft Excel Misc 1 27th Aug 2008 06:33 PM
Change colour of cells when content is altered/changed BUT NOT TO INSERTED OR DELETED ROWS Martin ©¿©¬ @nohere.net Microsoft Excel Misc 3 12th Apr 2008 05:13 PM
Relative Ranges - Multiple Cells =?Utf-8?B?SGVsZW4=?= Microsoft Excel Programming 1 12th Jul 2006 12:55 PM
How can a video be inserted with a relative path? =?Utf-8?B?TWFyaW8gQXZpbGVz?= Microsoft Powerpoint 1 12th Jul 2005 01:57 PM
select ranges of cells relative to location and then paste Buffyslay Microsoft Excel Programming 3 22nd Jun 2004 05:00 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 04:44 AM.