Johnny
I just realised that if you are adding rows or already have run the macro to
get partial results then I should have rewritten the code. Ig note the one
above and use the following:
Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row
For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsEmpty(d) Or IsEmpty(c) Then
' do nothing
ElseIf IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) < Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub
Regards
Peter
"Billy Liddel" wrote:
Sorry I have been off line for a while. If you have not gor the answer from
someone else try this
Sub InsertRow()
Dim c, d, nr As Long
'nr = Range("A1").CurrentRegion.Rows.Count
nr = Range("A65536").End(xlUp).Row
For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) < Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub
Just a different method of choosing the last cell - hope this helps
Peter
"Johnny" wrote:
after rows 40. the number is 9,10,11,12,13........ how to modify the macro to
insert blank rows after those number.
"Johnny" wrote:
Hi Peter,
the macro only insert blank rows up to row 40, no blank line afterthat. how
could I modify the macro.
Thx
"Johnny" wrote:
Hi Peter,
I got it, thank you so much.
your are the best.
ps. I still have anther problem sample below:
This is what I have in my work sheet.
|column column
row | A B
-----|----------------------------------
1 | 9 13
2 | 9 1
3 | 9 15
4 | 9 16
5 | 10 2
6 | 11 1
7 | 11 2
8 | 12 1
9 | 13 5
This is what I want to after formula.
the formula only replaced rows 6-8( column A-B).
|column column
rows| A B
-----|----------------------------------
1 | 9 13
2 | 9 1
3 | 9 15
4 | 9 16
5 | 10 2
6 | text N101
7 | text N102
8 | text N103
9 | 13 5
regards
Johnny.
"Billy Liddel" wrote:
Johnny
the macro needs to be copied into a VB Module. Hold Alt and press the F11
function key. Choose Insert, Module copy the code into the module and then
press Alt + Q to return to the workbook.
Activate the sheet with data and choose Tools Macro (or Alt + F8) select the
macro and click run.
the macro assumes that the data starts at A1 and that there are no blank
rows. If this is not the case it will have to be rewritten.
Peter
"Johnny" wrote:
Hi peter,
I had copy your macro and run it, but nothing hapen.
(what I did is copy your macro and paste and run)
am I do right?
regards
Johnny.
"Billy Liddel" wrote:
Hi Johnny
try this macro
Sub InsertRow()
Dim c, d, nr As Long
nr = Range("A1").CurrentRegion.Rows.Count
For i = nr To 2 Step -1
Cells(i, 1).Select
c = Cells(i, 1).Value: d = Cells(i - 1, 1).Value
If IsNumeric(c) And IsNumeric(d) And _
Cells(i, 1) < Cells(i - 1, 1) Then
Selection.EntireRow.Insert
End If
Next i
End Sub
Regards
Peter
"Johnny" wrote:
that will help a little, thanks
"FSt1" wrote:
hi
formulas return values. they can not perform actions like insert row.
there is a keyboards shortcut for it.
ctrl+ plus sign - insert a cell
crtl+ minus sign - removes a cell
high light row ctrl+ plus sign - adds a row
high light row ctrl+ minus sign - removes a row
regards
FSt1
"Johnny" wrote:
Hi
anyone know how to insert a blank rows by use formula at cell A between 1&2,
2&3, 3&4.
cell
A B
--------------------------------
1 7
1 8
2 1
2 2
2 6
2 7
3 7
3 15
3 16
3 3
4 3
4 4
Thanks..