Excel vba code understand

Posted on 2011-05-07
Last Modified: 2012-06-27
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,

Question by:SameerMirza

    Author Comment

    sorry If I sound to annoying but I just want to understand it once very clearly because lines like these are used quiet often
    LVL 43

    Accepted Solution

    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

    Author Comment

    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


    Author Comment

    how do we convert string to number?
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    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

    Author Closing Comment


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now