• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code: How to find last row & column (showing in Range) in a worksheet

inddon

Member
Hello There,

I have certain columns in a worksheet. There can be a possibility additional columns will be added.

I would like to have a VBA code to find out the last row and last column, which will give me a range. This range I would like to use it in a another code.

Could you please help?


Thanks & greetings
Don
 
Hello There,

I have certain columns in a worksheet. There can be a possibility additional columns will be added.

I would like to have a VBA code to find out the last row and last column, which will give me a range. This range I would like to use it in a another code.

Could you please help?


Thanks & greetings
Don

Have a look at these.

Code:
'Last row in a column. A in this case
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Last column in a row. Row 1 in this case
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Last used column on a worksheet
LastCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

'Last used row on a worksheet
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Last row for a range of columns. Note that A1 in this must be in the columns
'being searched
LastRow = Columns("A:F").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Last column for a group od rows.
LastCol = Rows("1:5").Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
 
Have a look at these.

Code:
'Last row in a column. A in this case
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Last column in a row. Row 1 in this case
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

'Last used column on a worksheet
LastCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

'Last used row on a worksheet
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Last row for a range of columns. Note that A1 in this must be in the columns
'being searched
LastRow = Columns("A:F").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Last column for a group od rows.
LastCol = Rows("1:5").Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column



Hello Mike,

Thank you for your reply and the code. I don't know how to define the type of variables. Is it possible if you could post the code with the procedure (eg. variables, types, etc.). It will help

Looking forward to hearing from you

Regards
Don
 
Hello Mike,

Thank you for your reply and the code. I don't know how to define the type of variables. Is it possible if you could post the code with the procedure (eg. variables, types, etc.). It will help

Looking forward to hearing from you

Regards
Don
Hi,

In every case the variable should be dimmed as LONG for example

Code:
Dim LastRow as Long
LastRow = Columns("A:F").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
 
Hi,

In every case the variable should be dimmed as LONG for example

Code:
Dim LastRow as Long
LastRow = Columns("A:F").Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row


Hi Mike,

Thanks for your quick reply.

I have a Table defined. Its range is from Column B to BK. The user is not familiar with the table concept, and does not want to use it. So I am giving him an option, where he can enter in a cell the number of rows he wants to extend the table. There will be a button 'Go'. When he presses this button, the code will check what is the last row and column range in the table (which will be derived from your advised code) . Then it will check what value the user entered in the cell (number of rows to extend) and taking that into account the table will be extended.

Eg.
1. Current range of table is "$B$5:$BK$7"
2. User enters in the cell 8 rows to extend.

ActiveSheet.ListObjects("Sheets").Resize Range("$B$5:$BK$9")

From your code, it gives a numeric value for the last used column. How can this be converted into a character value?

Please advise.

Thanks & regards
Don
 
Hi Mike,

Thanks for your quick reply.

I have a Table defined. Its range is from Column B to BK. The user is not familiar with the table concept, and does not want to use it. So I am giving him an option, where he can enter in a cell the number of rows he wants to extend the table. There will be a button 'Go'. When he presses this button, the code will check what is the last row and column range in the table (which will be derived from your advised code) . Then it will check what value the user entered in the cell (number of rows to extend) and taking that into account the table will be extended.

Eg.
1. Current range of table is "$B$5:$BK$7"
2. User enters in the cell 8 rows to extend.

ActiveSheet.ListObjects("Sheets").Resize Range("$B$5:$BK$9")

From your code, it gives a numeric value for the last used column. How can this be converted into a character value?

Please advise.

Thanks & regards
Don
Hi,

I wouldn't do it like that, I would use LISTROWS.ADD and let Excel do the working out of where the rows are. Say your user put a number in range L1 we can do this.

It's the same for adding columns, see the commented out line.

Code:
Sub Somesub()
Dim x As Long
For x = 1 To Range("L1").Value
ActiveSheet.ListObjects("Sheets").ListRows.Add
Next


'ActiveSheet.ListObjects("sheets").ListColumns.Add

End Sub
 
Back
Top