Viewing 3 posts - 1 through 3 (of 3 total)
  • Exel help, VBA coding
  • mrmo
    Free Member

    nicked this from another website and i think it’ll do what i need it to do, but i do need to tweak it, so as someone here is bound to know.

    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets(“PartsData”)

    ‘find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

    The question is the second phrase, what is it telling me, how does it work. It says find first empty cell but how does it do this?

    Free Member

    It is doing

    Rows.Count = the maximum number of rows that can possibly exist in excel – so it selects the very bottom cell, column 1, (that will be R65536 C1)

    .End(xlUp) is doing the same as pressing End+Up together, which goes up from the referenced cell until it finds something, so it’ll reference the bottom most cell in the first column.

    .Offset(1,0) offsets down by 1

    It is a bit gross, but some people do it like this. The nice way is to use

    Set rngUsedRange = ActiveSheet.UsedRange
    intTopRow = rngUsedRange.Rows.Row
    intNumRows = rngUsedRange.Rows.Count
    ActiveSheet.Cells(intNumRows + intTopRow, 1).Select

    this does something subtly different, in that it selects the first row that has nothing in, rather than the first cell in the first row with nothing in, but that is usually what people actually want.


    Free Member

    just to check, cells() allows you to pin point a cell, by Row then Column. and offset() has the same argument.

    So Offset(1,0) is go down a row and offset(0,1) is go across one column. I assume positive is down and negative is up? so offset(-1,0) is acceptable?

    Can you just expand on the second example. Activesheet.usedrange. i assume this gives both a column and a row that has been used?

    Just trying to get my head round the next two lines.

    rngusedrange.rows.row ? not sure of the meaning of this.

    I assume the next line, rngusedrange.rows.count means look at rngusedrange and tell me how many rows have data in them, in a related way it could be rngusedrange.columns.count to tell how many columns have been used?

    the last line is telling excel that the active cell is the row decided by the above and column one?

Viewing 3 posts - 1 through 3 (of 3 total)

The topic ‘Exel help, VBA coding’ is closed to new replies.