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:

 

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:

Find last row column cell in an Excel worksheet with VBA

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *