Last Cell in a column

[gemma-the-husky]

Struggling to manipulate a spreadsheet

I have a block of cells = eg A1:L254, although there is then further data beneath this block.

I want to find the last populated row in the block (ie 254)

I have code to find the maximum extent of the spreadsheet, but as there is stuff below the datablokc, this doesnt quite work.

Over time, new rows may be entered/deleted, so the last item wont then be in row 254, so I cant hard code it.

----------
FIRST APPROACH

is there an easy vba code to examine the block to do this. I can give the data block a name if that helps

----------
ALTERNATIVE APPROACH

As I cant do this yet, as an aternative to this, I am storing the row number I need in a separate cell (B258). which is therefore the last populated cell in column B. But as I add/delete rows in the data block, this moves the position of cell B258 up or down respectively. At the moment my code examines B258 to get the range - but as I insert/delete rows, the right cell yto use wont be B258.

So instead, is there a way of finding the last populated cell in a given column, with code

-------------

Thanks in anticipation

 

[namliam]

Range("B1").Select
Selection.CurrentRegion.Select

Or have you tried that?? You dont specify what kind of code you have that doesnt work

Or...
Range("B1").Select
Selection.End(xlDown).Select

Providing column B is always populated.

 

[gemma-the-husky]

thanks

i diudnt have any code that didnt work - i just wasnt sure where to start

i played around a bit after posting, and got smilar code from another thread which i put in to a function. then i realised you cant have blanks in the data block as you pointed out - but its working ok anyway.

function findlastrow as long
Sheets("sheet1").Select
Range("A2").Select
Selection.End(xlDown).Select
findlastrow = ActiveCell.Row
end function


excel manipulation seems far harder than with access - or is that im just not familiar with excel? it took ages to work out how to get buttons in and out of edit mode

 

[namliam]

If you have like a table, with empty cells here and there.... but still mostly data in it...

Try using the CurrentRegion ....

Excel is no harder when you are used to it... but Excel vs Access is just a totaly different world... No things that work in Excel migrate (well) to access and vice versa.

 

[Brianwarnock]

findlastrow = Worksheets("sheet1").Range("A2").End(xlDown).Row

and you may also be interested in

Function lastinregion()

Worksheets("sheet1").Activate
ActiveCell.CurrentRegion.Select
With Selection
lastcol = .Cells(1, 1).Column + .Columns.Count - 1
lastrow = .Cells(1, 1).Row + .Rows.Count - 1
Set lastcell = .Cells(.Rows.Count, .Columns.Count)
End With
MsgBox lastcell
MsgBox lastcol
MsgBox lastrow
MsgBox lastcell.Address()

End Function


Brian