Many times we need to find out the last row of excel sheet which is used by data. This has many uses such as to
Add new column to the data only up to the last used row of exiting dateAdd new row to the data immediately after the last row so as you do not over write the exiting data.
See the code below for the doing the activities as listed above.
Sub LastRowOfData() '*************************************************************************************************** '* Macro recorded by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on September 4, 2009 * '*************************************************************************************************** If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False End If LastRow = GetLastRowWithData ' Getting the Row number Range("C3").Select ' Select the header row of the data in new column ActiveCell.FormulaR1C1 = "Col 2" ' Add new column header Range("C4").Select ' Select the starting column where you need to add data ActiveCell.FormulaR1C1 = "XYZ" ' Add Data Value or Formula Range("C4:C" & LastRow).Select ' This will select the column from starting row to Last row of data Selection.FillDown ' This will fill the data downwards from First row i.e. Formula or value added by you Range("B" & LastRow + 1).Select ' This selects the starting cell of row next to last row of exiting data End Sub
'******************************************************************** '* UDF to Get Last Row with Data on worksheet * '******************************************************************** Public Function GetLastRowWithData() As Long
Dim ExcelLastCell As Object, lRow As Long, lLastDataRow As Long, l As Long
Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell) lLastDataRow = ExcelLastCell.Row lRow = ExcelLastCell.Row ' Do While Application.CountA(ActiveSheet.Rows(lRow)) = 0 And lRow <> 1 lRow = lRow - 1 Loop ' lLastDataRow = lRow
GetLastRowWithData = lLastDataRow
End Function
|
Function GetLastRowWithData gives inaccurate results if you have active filters on the worksheet. That is why I have included code to remove active filters from the worksheet before using this function to get last row of data.
Download sample file to check how it works.
There is one more option which gives you satisfactory results
LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Take care to remove filters before using above code
There are some more options to find last row however are not very reliable as they give you last row of the worksheet irrespective of fact that row could be a empty row.
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
or
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
Select Last Row , Find Last Row , VBA Last Row , VBA Find Last Row , Excel Last Row
Hi Sir,
ReplyDeleteI need some help on Excel 2010. I need to copy the last line of data in one worksheet and paste on to another worksheet to a specific area, how can I write a macro to do this?
Your help would be appreciated.
Kind regards
Sam
Excel has some very amazing features which can make data management and analysis easy. My current job has a requirement of a professional level of excel but I only know basic, so I was desperately looking for some help. But before that, I need an online term paper writing service for my academic help so that I can spare free time to learn excel.
ReplyDeleteI think, this is a magic because many readers surprised to see it. Before this, they had not enough knowledge about it. Now, they will follow it and make excel rows in very short time. Masters dissertation writing service.
ReplyDeleteAh, my brother was looking at some excel tutorials and tips. I think this post might help him out. Meanwhile, I have to look for someone whom I can pay for dissertation UK style and have him write my dissertation for me. I think I should save this post as well – it might come in handy – I mean, I often struggle with excel, so why not save it?
ReplyDeletelast row of the worksheet irrespective of ptions to find last row however are not very reliable fact that row could be a empty row.
ReplyDeleteThere are some more o as they give youAccording to a survey, about 78 per cent of students in the United Kingdom are doing side jobs in order to manage their daily living expenses and tuition fee.
Dissertation Writing Services
Get online with the biggest gaming player and play judi online slots with you and your friend.
ReplyDeleteThat's what I'm looking for to complete the last part of academic task, now I don't have to took academic service from anyone because finally I found solution of my query.
ReplyDeleteget custom leatherjackets at swankygarments All Of Us Are Dead 2022 Students Uniform Green Sweater
ReplyDelete80s Fashion Trends Baggy Blue Or Purple Parachute Jacket
Danny Warhol Home Alone Little Nero’s Pizza Boy Varsity Jacket
Your post gives information about Excel Macros : Find Last Row. I noticed my brother studying various Excel tutorials and advice. I believe he will benefit from this information. I need to find a writer in the UK who I can Assignment aid while I look for someone else to do it. I believe I should also bookmark this article because it may be useful. After all, I struggle with Excel all the time, so why not?
ReplyDelete
ReplyDeleteI've learned a lot of new stuff from this website. I read your site because you provide the greatest guidance for everyone, and I appreciate the analytical essay help service because I find it useful for my academic work. I just want to say again how much of an inspiration you are. I'd want to see more online content like this.
Thanks for sharing this Helpful information! Regards : https://lakeviewhotelkirkland.com
ReplyDeleteThat was really helpful to me! 4thquarterrealtygroup.org
ReplyDeleteI came here and get information according to my concern and may be i got success after reading your blog. Kindly update more blog like this thanks for sharing.
ReplyDeleteVisit for discount: https://infovouchers.com/store/ooni-discount-code
Your posts really bring positivism to a task that sometimes looks overwhelming.
ReplyDeleteRegards : https://bakershomecleaning.com/services/moving-in-out-cleaning-services/
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDelete