How to get row number?
I use the "Do Until...Loop" in forms subroutine to insert a row in worksheet
at specified location. How do I get the row number of the new row? Private Sub cmdInsertRow_Click() Dim ws As Worksheet Dim a As Long Set ws = Worksheets("Sheet1") a = 22 'data starts at row 22 Do Until Trim(ws.Cells(a, 1)) = "" If ws.Cells(a, 1) = Me.cboRefValue.Value Then ws.Cells(a + 1, 1).EntireRow.Insert a = a + 2 Else a = a + 1 End If Loop End Sub |
How to get row number?
Hi Sam,
After the code where you insert the row, the new row number should be a+1. -- Regards, OssieMac "Sam Kuo" wrote: I use the "Do Until...Loop" in forms subroutine to insert a row in worksheet at specified location. How do I get the row number of the new row? Private Sub cmdInsertRow_Click() Dim ws As Worksheet Dim a As Long Set ws = Worksheets("Sheet1") a = 22 'data starts at row 22 Do Until Trim(ws.Cells(a, 1)) = "" If ws.Cells(a, 1) = Me.cboRefValue.Value Then ws.Cells(a + 1, 1).EntireRow.Insert a = a + 2 Else a = a + 1 End If Loop End Sub |
How to get row number?
Yes! Thanks OssieMac :-)
Sam "OssieMac" wrote: Hi Sam, After the code where you insert the row, the new row number should be a+1. -- Regards, OssieMac "Sam Kuo" wrote: I use the "Do Until...Loop" in forms subroutine to insert a row in worksheet at specified location. How do I get the row number of the new row? Private Sub cmdInsertRow_Click() Dim ws As Worksheet Dim a As Long Set ws = Worksheets("Sheet1") a = 22 'data starts at row 22 Do Until Trim(ws.Cells(a, 1)) = "" If ws.Cells(a, 1) = Me.cboRefValue.Value Then ws.Cells(a + 1, 1).EntireRow.Insert a = a + 2 Else a = a + 1 End If Loop End Sub |
How to get row number?
Just as a follow up, you don't need to run a loop to do what your posted
code does. Give this a try (it will be more efficient than your loop method, especially if the search list is long)... Private Sub cmdInsertRow_Click() Dim Result As Range Dim NewRow As Long Const DataStartRow As Long = 22 With Worksheets("Sheet1") Set Result = .Cells(DataStartRow, "A").Resize(.Rows.Count - _ DataStartRow + 1, 1).Find(Me.cboRefValue.Value, _ LookIn:=xlValues) End With If Not Result Is Nothing Then Result.EntireRow.Offset(1, 0).Insert NewRow = Result.Row + 1 MsgBox "A blank row was added at Row #" & NewRow Else MsgBox "No row was added." End If End Sub You can, of course, eliminate the two MessageBox statements (and the Else block)... I just included them for demonstration purposes. Rick "Sam Kuo" wrote in message ... Yes! Thanks OssieMac :-) Sam "OssieMac" wrote: Hi Sam, After the code where you insert the row, the new row number should be a+1. -- Regards, OssieMac "Sam Kuo" wrote: I use the "Do Until...Loop" in forms subroutine to insert a row in worksheet at specified location. How do I get the row number of the new row? Private Sub cmdInsertRow_Click() Dim ws As Worksheet Dim a As Long Set ws = Worksheets("Sheet1") a = 22 'data starts at row 22 Do Until Trim(ws.Cells(a, 1)) = "" If ws.Cells(a, 1) = Me.cboRefValue.Value Then ws.Cells(a + 1, 1).EntireRow.Insert a = a + 2 Else a = a + 1 End If Loop End Sub |
How to get row number?
Thanks Rick. I've learnt something here :-)
Sam "Rick Rothstein (MVP - VB)" wrote: Just as a follow up, you don't need to run a loop to do what your posted code does. Give this a try (it will be more efficient than your loop method, especially if the search list is long)... Private Sub cmdInsertRow_Click() Dim Result As Range Dim NewRow As Long Const DataStartRow As Long = 22 With Worksheets("Sheet1") Set Result = .Cells(DataStartRow, "A").Resize(.Rows.Count - _ DataStartRow + 1, 1).Find(Me.cboRefValue.Value, _ LookIn:=xlValues) End With If Not Result Is Nothing Then Result.EntireRow.Offset(1, 0).Insert NewRow = Result.Row + 1 MsgBox "A blank row was added at Row #" & NewRow Else MsgBox "No row was added." End If End Sub You can, of course, eliminate the two MessageBox statements (and the Else block)... I just included them for demonstration purposes. Rick "Sam Kuo" wrote in message ... Yes! Thanks OssieMac :-) Sam "OssieMac" wrote: Hi Sam, After the code where you insert the row, the new row number should be a+1. -- Regards, OssieMac "Sam Kuo" wrote: I use the "Do Until...Loop" in forms subroutine to insert a row in worksheet at specified location. How do I get the row number of the new row? Private Sub cmdInsertRow_Click() Dim ws As Worksheet Dim a As Long Set ws = Worksheets("Sheet1") a = 22 'data starts at row 22 Do Until Trim(ws.Cells(a, 1)) = "" If ws.Cells(a, 1) = Me.cboRefValue.Value Then ws.Cells(a + 1, 1).EntireRow.Insert a = a + 2 Else a = a + 1 End If Loop End Sub |
All times are GMT +1. The time now is 02:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com