Help with some code to insert rows
After posting I decided to have a go myself and came up with the following,
which appears to work:
Sub SortCopyFormat()
Application.ScreenUpdating = False
With Worksheets("Hidden")
For Each cell In .Range("A2:A" & .Range("A65536").End(xlUp).Row)
numrow = Application.Max(cell.Offset(0, 13), cell.Offset(0, 24),
cell.Offset(0, 35), cell.Offset(0, 48), cell.Offset(0, 59), cell.Offset(0,
70), cell.Offset(0, 82)) - 1
If numrow = 1 Then
cell.Offset(1, 0).Resize(numrow, 1).EntireRow.Insert
End If
Next
End With
Application.ScreenUpdating = True
End Sub
Is there any way to simplify/improve it?
Gareth
"Tom Ogilvy" wrote in message
...
mymax = Application.Max(Range("N:N,Y:Y,AJ:AJ,AW:AW,BH:BH,B S:BS,CD:CD"))
if you mean for that specific row (assume i is the row you want to check)
set rng =
Intersect(rows(i),Range("N:N,Y:Y,AJ:AJ,AW:AW,BH:BH ,BS:BS,CD:CD"))
myMax = Application.Max(rng)
--
Regards,
Tom Ogilvy
"Gareth" wrote in message
...
I want to find the maximum value in columns N, Y, AJ, AW, BH, BS and CD
It can from 1 to 10, depending on th value I want to insert a row below.
I had thought of starting it sometrhing like this:
For Each cell In Range("A2:A" & Range("A65536").End(xlUp).Row)
'find the max minus 1
cell.offset(1,0).resize(max minus 1,1).EntireRow.Insert
Next cell
I cannot work out the code to calculate the max -1 figure.
Any help gratefully received.
Gareth
|