How to Add or Manipulate Groups of Multiple Rows

[Arnold]

Hi All,
In a scheduling worksheet, first and last names are listed in the left
columns, formulas in columns to the right of the names, and then
individual days are added daily in columns to the right of the
formulas. Marks are made in the intersections of days and people.

I would like to have a cmd button or menu to add new people. The
tricky part is that 1 person's info occupies 4 consecutive rows. Each
row contains different formulas that would have to be included in the
copy. It would be best if users could click on any 1 of the 4 rows of
an existing person's record, and then click the cmd to add a new
person's record (4 new rows) either above or below the existing
person's record. Code would have to recognize where one group of rows
starts and ends.

Note that each person's name is put on the top row (cols A and B) of
his or her record, so the presence of text 0 or 1 or 2 or 3 lines
above where the cursor is could be the identifying feature that could
maybe make this happen. I just don't know how. Any help would be
greatly appreciated.

Thanks,
Eric

 

[Bernie Deitrick]

Eric,

Try the code below. Assumes that last names are in column B, first names in A.

HTH,
Bernie
MS Excel MVP


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(4).EntireRow
If MsgBox("Above = ""Yes"", Below = ""No""", vbYesNo) = vbYes Then
.Copy
.Insert
myCell.Offset(-4).Value = newFName
myCell.Offset(-4,1).Value = newLName
Else
.Copy
.Offset(4).Insert
myCell.Offset(4).Value = newName
myCell.Offset(4,1).Value = newLName
End If
End With

Application.CutCopyMode = False
End Sub

 

[Arnold]

Thanks for responding Bernie. I will have time to try this out later
today and will post back the results.
Eric

 

[Arnold]

I tried this code and it will allow a user to insert a row for a new
person below or above the current cell position.

However, if the current cell is within another person's record (4
consecutive rows), the new row will split this and be inserted within
the record. I would like for the new person's row to be entered
either before the current record's top row (contains the the person's
first and last names in cols A and B), or below the current record's
last row (3 rows down from the row with the first and last names in
cols A and B).

Also, the code above only copies formulas for the current row and
carries them into a new row--but each of the 4 rows of a person's
record contain different formulas. I would like for code to carry all
of the formulas in the 4 different rows into 4 new rows properly--
formulas should remain but values should revert back to null or
default values.

I thought about putting a template range of 4 rows containing
formatting and formulas on a separate sheet, but this won't work
because days will be added in the main sheet, so the template will not
match up when inserted into the schedule sheet with code.

Thanks again
Eric

 

[Bernie Deitrick]

Arnold,

If you are seeing the behavior that you describe, then the first column of
your record isn't truly blank.

Try selecting a cell in one of those rows and running this macro:

Sub Test()
MsgBox IIf(Cells(ActiveCell.Row, 1).Value = "", _
"The cell is blank", "The cell only looks blank")
End Sub

You will need to delete the spaces or other values in the seemingly blank
cells.

HTH,
Bernie
MS Excel MVP

 

[Arnold]

Ah, you're right. All 4 rows of of a person's record in my test
spreadsheet had the person's name (as in a non-normal flat structure)
for sorting purposes. Thanks Bernie.