How to find the last row used in Excel worksheet even if data is missing in any column in the row using the Find function. If we use the standard formula to find the next blank row as shown below we may face some problems if data in the first or ‘A’ column is missing:
erow = sheet1.Cells(Rows.count, 1).End(xlUp).Offset(1, 0).Row
However if we use the following formula which uses the ‘FIND’ function we can easily find the last row used even if some columns in the row don’t have data:
lastRow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Watch the video below to understand the concept:
Watch this video on YouTube.
Here’s the complete VBA code:
Dim numbertxt As Long
Private Sub UserForm_Initialize()
Dim i As Long
numbertxt = InputBox(“Enter no of text-boxes and labels you wish to create at run-time”, “Enter TextBox & Label Number”)
Dim txtB1 As Control
For i = 1 To numbertxt
Set txtB1 = Controls.Add(“Forms.TextBox.1”)
With txtB1
.Name = “txtBox” & i
.Height = 20
.Width = 50
.Left = 70
.Top = 20 * i * 1
End With
Next i
Dim lblL1 As Control
For i = 1 To numbertxt
Set lblL1 = Controls.Add(“Forms.Label.1”)
With lblL1
.Caption = “Label” & i
.Name = “lbl” & i
.Height = 20
.Width = 50
.Left = 20
.Top = 20 * i * 1
End With
Next i
Dim q As Long
For q = 1 To numbertxt
Controls(“lbl” & q) = Cells(1, q)
Next q
Dim lstbox As Control
numberlst = InputBox(“Enter no of list-boxes you wish to create”, “ListBoxes Number”)
For r = 1 To numberlst
Set lstbox = Controls.Add(“Forms.ListBox.1”)
With lstbox
.Name = “List” & r
.Height = 60
.Width = 50
.Left = 150
.Top = 20
End With
If lstbox.Name = “List1” Then
Controls(“List1”).List = Array(“Tata”, “Vodafone”, “Airtel”, “Jio”)
ElseIf lstbox.Name = “List2” Then
lstbox.Left = 220
Controls(“List2”).List = Array(“Two”, “Three”, “Four”, “Five”, “Six”)
End If
Next r
End Sub
Private Sub CommandButton1_Click()
Dim p As Long
Dim erow As Long
Dim lastRow As Long
lastRow = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
‘erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
MsgBox lastRow
‘MsgBox erow
For p = 1 To numbertxt
Cells(lastRow + 1, p) = Controls(“txtBox” & p).Text
Next p
Cells(lastRow + 1, 6) = Controls(“list1”).Value
Cells(lastRow + 1, 7) = Controls(“list2”).Value
End Sub
Private Sub CommandButton3_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ListBox” Then
ctl.Value = “”
End If
Next ctl
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Further reading:
