How to get row number?

[Sam Kuo]

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

 

[OssieMac]

Hi Sam,

After the code where you insert the row, the new row number should be a+1.

 

[Sam Kuo]

Yes! Thanks OssieMac

Sam

Hi Sam,

After the code where you insert the row, the new row number should be a+1.

 

[Rick Rothstein \(MVP - VB\)]

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]

Thanks Rick. I've learnt something here

Sam

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