insert cells macro

  • Thread starter Thread starter Kelly McFaul
  • Start date Start date
K

Kelly McFaul

Hi,
I have a column of data that I need to insert 4 blank cells after each
entry.
I can figure out how to write a macro to insert one cell, but not four.
These values are imported from data loggers that monitor temperatures of
different things. Some readings are taken every 2 minutes, some every 10
minutes. What I am hoping to accomplish is to paste the readings taken every
10 minutes next to the readings taken every 2 minutes and have the times
correspond to each reading like this:
col A col b col c
10:00 68 72
10:02 69
10:04 72
10:06 65
10:08 70
10:10 67 74
and so forth.
The number of rows in this column would vary.
Can this be done? I might be in way over my head on this one, but the guy I
work for thinks I can do ANYTHING and I hate to let him down!!
Thanks in advance for any suggestions.
Kelly
 
Here is one I posted recently for 10 rows

Sub insert10rows()
For i = 3 To 2 Step -1
Cells(i, "d").Resize(10, 1).EntireRow.Insert
Next i
End Sub
 
Thanks very much for the quick response. However, I need to insert cells,
not rows. Columns A & B need to stay the same, while column c is where I
would want to insert the blank cells.
KElly
 
this should do it. Change the 30 to whatever you rlast row is.

Sub insertCells()
For i = 30 To 2 Step -1
Cells(i, "c").Resize(4, 1).Insert shift:=xlDown
Next i
End Sub
 
Thank you!!
That worked perfectly. It's a bit slow, as I have 4400 rows of data, but it
did exactly what I needed it to do.
Thanks so much for the help.
Kelly
 
Kelly

no improvement on the code, but this might improve the speed of execution:

Sub insertCells()
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 30 To 2 Step -1
Cells(i, "c").Resize(4, 1).Insert shift:=xlDown
Next 'i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Regards

Trevor
 
Kelly,

If you haven't done it already you can turn off calculation and screen updating
while the macro runs. It makes quite a difference on execution speed.

Sub test873()
Dim calcState As Integer
calcState = Application.Calculation
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

' your code here

Application.Calculation = calcState
Application.ScreenUpdating = True
End Sub

HTH
Anders Silven
 
to the OP: I'd add something like this line, too:

ActiveSheet.DisplayPageBreaks = False

Excel wants to determine where to draw those dotted lines (pagebreaks) each time
you delete/add a row (or column).

If you turn this display off, then excel won't bother (well, until you do your
next print/printpreview).
 
Back
Top