November 5, 2017

# How to Find Last Row Used in Excel Worksheet with Blank Columns

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:

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
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
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
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()