Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif number is greater than a number but less than another number | Excel Discussion (Misc queries) | |||
How can I click on a telephone number in an Excel 2002 spreadsheet, and have the number dialed? | Excel Discussion (Misc queries) | |||
How to calculate number of occurencies of a specific number number | Excel Discussion (Misc queries) | |||
find an exact number in a different sheet and use the cell containing the number to output information | Excel Programming | |||
Rounding a number to a multiple quantity that adds to a fixed total number | Excel Worksheet Functions |