• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 212
  • Last Modified:

Excel vba code understand

I am trying to understand the code below,
iRow = ws.Cells(Rows.Count, 1). end(xlUp).Offset(1,0).Row

does it mean somthing like
worksheet.Cells(total rows, first cell).Offset(next line, same cell).Row

Q1. why we use .Row at the end
Q2. How would we find last empty cell - col

Kind regards,

  • 4
  • 2
1 Solution
SameerMirzaAuthor Commented:
sorry If I sound to annoying but I just want to understand it once very clearly because lines like these are used quiet often
Saqib Husain, SyedEngineerCommented:
rows.count gives the number of rows in a sheet

ws.Cells(Rows.Count, 1) is the last row in column 1

ws.Cells(Rows.Count, 1). end(xlUp) If you put the cursor in the last row of column 1 and press <end> then <up> the cell where you would land will be given by this expression

ws.Cells(Rows.Count, 1).end(xlUp).row gives the row number of this cell.

So, usually, if the last row of your data in row 487 then
ws.Cells(Rows.Count, 1). end(xlUp)
will take you to A487


ws.Cells(Rows.Count, 1). end(xlUp).row will give the row number of A487 or simply 487
SameerMirzaAuthor Commented:
Orite.. - now I know and I can simple write the same statement for last occupied cell if I have to - JUST PERFACT

I have another question though,
I am following an example - please have a look at the code below
Its basically to enter values values in excel sheet - database - using a command button

Know I know that we can use 'Exit Function' to exit function
But I am trying to implement the error handling here but not sure
For example it throws the 'alert' when 'partid' is not given but would still write the row - which it shouldn't

It would great if you get me started on it or just quickly edit the code to be able to handle the 'nulls' etc


Private Sub cmdAdd_Click()

Dim iRom As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'find first empty row in the database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.txtPart.Value) = "" Then
MsgBox "Please enter part number"
End If

'assign the values
ws.Cells(iRow, 1).Value = Me.txtPart.Value
ws.Cells(iRow, 2).Value = Me.txtLocation
ws.Cells(iRow, 3).Value = Me.txtDate
ws.Cells(iRow, 4).Value = Me.txtQuantity

'clear the data
Me.txtPart.Value = ""
Me.txtLocation = ""
Me.txtDate = ""
Me.txtQuantity = ""

End Sub

Open in new window

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

SameerMirzaAuthor Commented:
how do we convert string to number?
Saqib Husain, SyedEngineerCommented:
Change line 15 from  "End if" to "Else"

and add "End if" on line 22

To convert string to number the conventional function is
a = val(string)

but quite often this is very conveniently done by

a = --string


a = string * 0
SameerMirzaAuthor Commented:

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now