Adding records with macro in Excel

[frosty7700]

How would I go about adding new rows to a spreadsheet using a macro? No data is to be added...rather, new rows with the proper formatting need to be added.

Right now I have a macro that addes 5 records, but it does so by copying a hidden template record and adding 5 records from the 6th row on...the problem is that it is hardcoded to those rows so it only works once. I need a way to do this, but by dynamically finding the last formatted row and adding from there.

Ideas?

 

[mmandk9]

frosty,

Is there any reason you can't just add the formatting to the spreadsheet when you create it? That way you don't have to go back and add it 5 rows at a time. I would simply record a new macro and add the formatting - the system will give you all the parameters. To adapt it, simply expand the range and pick out what you need/substitute variables as appropriate.

Otherwise, if you have to add 5 rows at a time, I would try this:

I shaded the range B41:C60 on the 'admin' sheet and want to find the last shaded/formatted row.

Dim r As Integer 'row variable
Dim c As Integer 'col variable
Dim LookingForRow As Boolean

r = 41
c = 2
LookingForRow = True

Do While LookingForRow
If Not Sheets("admin&quot.Cells(r, c).Interior.ColorIndex = 15 Then
MsgBox "Start at row " & r, vbOKOnly
'insert formatting code here
LookingForRow = False ' or an "Exit Do" statement
Else
r = r + 5
End If
Loop

The message box returned: Start at row 61.
This would also work for columns by adding to the c variable instead of the r variable.

HTH - Let me know.

MM

 

[frosty7700]

"Is there any reason you can't just add the formatting to the spreadsheet when you create it? That way you don't have to go back and add it 5 rows at a time."

It's just the specs I was handed. I actually just took another look at it and figured it out (quite similar to your code, actually). Didn't think about using Colorindex though. Thanks for the tip.