Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
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?
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
ActiveSheet.UsedRange
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.
Joe
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?