Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Insert Row macro not working
I have the following macro set up but it keeps hanging up on the first
indented line. Sub Insertrow() ' ' Insertrow Macro ' Macro recorded 10/6/2005 by DCTC ' ' Keyboard Shortcut: Ctrl+i ' Dim iLastRow As Long Dim i As Long iLastRow = ActiveSheet.Cells(Rows.Count, "F").End(x1Up).Row For i = iLastRow To 1 Step -1 If Cells(i, "F").Value = "Total" Then Rows(i + 1).EntireRow.Insert End If Next i End Sub |
#3
|
|||
|
|||
Try using
iLastRow = Range("F1").End(xlDown).Row rather than iLastRow = ActiveSheet.Cells(Rows.Count, "F").End(x1Up).Row Best rgds Chris Lav "Don Guillett" wrote in message ... a cursory look suggests you need to change 1 to l -- Don Guillett SalesAid Software "Acct Supr - DCTC" wrote in message ... I have the following macro set up but it keeps hanging up on the first indented line. Sub Insertrow() ' ' Insertrow Macro ' Macro recorded 10/6/2005 by DCTC ' ' Keyboard Shortcut: Ctrl+i ' Dim iLastRow As Long Dim i As Long iLastRow = ActiveSheet.Cells(Rows.Count, "F").End(x1Up).Row For i = iLastRow To 1 Step -1 If Cells(i, "F").Value = "Total" Then Rows(i + 1).EntireRow.Insert End If Next i End Sub |
#4
|
|||
|
|||
Hi Chris,
The poster is much better off correcting the original as Don indicated but the poster might still not see right away that it was an x1 prefix incorrectly used as an xl prefix to a builtin constant. Took me a while to get that right when seeing posted solutions and help files. The reason the orginal is better is because it starts from the bottom of the column and looks for the first nonempty cell to find your last used in the column. The reason the second one is bad is because it starts from the top and finds the first empty cell -- you could easily have an empty cell in a column including from having an empty row to separate data or to provide for filling in data later. Of possible interest http://www.mvps.org/dmcritchie/excel/insrtrow.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Chris Lavender" wrote in message ... Try using iLastRow = Range("F1").End(xlDown).Row rather than iLastRow = ActiveSheet.Cells(Rows.Count, "F").End(x1Up).Row Best rgds Chris Lav "Don Guillett" wrote in message ... a cursory look suggests you need to change 1 to l -- Don Guillett SalesAid Software "Acct Supr - DCTC" wrote in message ... I have the following macro set up but it keeps hanging up on the first indented line. Sub Insertrow() ' ' Insertrow Macro ' Macro recorded 10/6/2005 by DCTC ' ' Keyboard Shortcut: Ctrl+i ' Dim iLastRow As Long Dim i As Long iLastRow = ActiveSheet.Cells(Rows.Count, "F").End(x1Up).Row For i = iLastRow To 1 Step -1 If Cells(i, "F").Value = "Total" Then Rows(i + 1).EntireRow.Insert End If Next i End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to insert row after specific text | Excel Discussion (Misc queries) | |||
asking again, macro to insert rows | Excel Worksheet Functions | |||
Help with macro looping and color query function | Excel Discussion (Misc queries) | |||
insert rows in excel spreadsheet via macro | Excel Worksheet Functions | |||
Macro not working with Excel 2003 | Excel Discussion (Misc queries) |