Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
I have a worksheet in which I have deleted rows at the end of the sheet in
some VBA code. In VBA I now want to find the last last row that contains data. Some columns have data and some are blank so I don't know which column will consistently have data. What is the correct code I need to use to return the last row number that contians data? TIA. -- Ken Hudson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
Hi,
Have a try with : Sub GetRealLastRow() Dim RealLastRow As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).row Cells(RealLastRow, 1).Select End Sub HTH |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
I use this function a lot in my projects. It returns the last cell. You can
easily get the row from that something like this... sub test msgbox lascell(sheets("Sheet1")).row end sub Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "Ken Hudson" wrote: I have a worksheet in which I have deleted rows at the end of the sheet in some VBA code. In VBA I now want to find the last last row that contains data. Some columns have data and some are blank so I don't know which column will consistently have data. What is the correct code I need to use to return the last row number that contians data? TIA. -- Ken Hudson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
jim
you have a typo in your sub msgbox lasTcell(sheets("Sheet1")).row -- Gary "Jim Thomlinson" wrote in message ... I use this function a lot in my projects. It returns the last cell. You can easily get the row from that something like this... sub test msgbox lascell(sheets("Sheet1")).row end sub Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "Ken Hudson" wrote: I have a worksheet in which I have deleted rows at the end of the sheet in some VBA code. In VBA I now want to find the last last row that contains data. Some columns have data and some are blank so I don't know which column will consistently have data. What is the correct code I need to use to return the last row number that contians data? TIA. -- Ken Hudson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
See also
http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ken Hudson" wrote in message ... I have a worksheet in which I have deleted rows at the end of the sheet in some VBA code. In VBA I now want to find the last last row that contains data. Some columns have data and some are blank so I don't know which column will consistently have data. What is the correct code I need to use to return the last row number that contians data? TIA. -- Ken Hudson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
Just what I wanted.
Thanks. -- Ken Hudson "Carim" wrote: Hi, Have a try with : Sub GetRealLastRow() Dim RealLastRow As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).row Cells(RealLastRow, 1).Select End Sub HTH |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
Folks:
I finally got my find last row formula to work but I can't seem to get it to work inside my macro with other variables. Note: My previous copy data was contiguous 1 region but now I need to copy non-contiguous copy data with the parameters in this code (B1:M & lastrow) Also, I want to use an array for worksheets so that its easier to add and remove cost centers. Finally, if I can get this licked, I'll need a separate macro with a formula for first row, since I have another set of data that resides below a table I don't need. Thank you - Rowland This worked: START CODE: Sub testing123() Dim LastRow As Long With Worksheets("4050CC30001") LastRow = Worksheets("4050CC30001").Cells(Rows.Count, "B").End(xlUp).Row .Range(Cells(1, 2), Cells(LastRow, 13)).Select End With End Sub END CODE Now how do I get that to work within this: START CODE: 'Completed Code ? Sample CC Option Explicit Sub Populate_line_item_Workbook_Browser_Method() Dim MasterWB As Workbook Dim SourceWB As Workbook Dim rngSrc As Range Dim rngDst As Range Dim ws As Worksheet Dim varFileName As Variant Dim I As Long Dim myArr As Variant Dim LastRow As Long Set MasterWB = Workbooks("Line items-Combined16.xlsm") ''''''''''Clear MasterWB'''''''''''''''''''''''''''''''''''''''''' ''''''''''' MasterWB.Sheets("Master-Incoming").Activate Rows("3:3").Select Range("E3").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("a1").Activate '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''' ?"4050CC30001", "301AA1234", "50BB9999" and "65961LL3201" myArr = Array("4050CC30001", "301AA1234", "50BB9999", "65961LL3201") 'For I = LBound(myArr) To UBound(myArr) ? Don?t know how to make array work varFileName = Application.GetOpenFilename(, , "Please select source workbook:") If TypeName(varFileName) = "String" Then Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0) For Each ws In SourceWB.Worksheets If ws.Name Like "4050CC30001" And ws.Visible < xlSheetHidden Or _ ws.Name Like "301AA1234" And ws.Visible < xlSheetHidden Or _ ws.Name Like "50BB9999" And ws.Visible < xlSheetHidden Or _ ws.Name Like "65961LL3201" And ws.Visible < xlSheetHidden Then 'Expand Column groups, Collapse Row groups ? need to hide lower table ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2 'copy ?This works for contiguous data region, but my new data is not contiguous: ?Set rngSrc = ws.Range("A3").CurrentRegion.Offset(1, 0) LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row ?I can?t get this to work: Set rngSrc = ws.Range(Cells(1, 2), Cells(LastRow, 13)) 'paste Set rngDst = MasterWB.Sheets("Master-Incoming").Range("A" & Rows.Count).End(xlUp).Offset(1) rngSrc.Copy rngDst.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If Next ws SourceWB.Close False ?On contiguous data, got error message when it ran out of new sheets so I _ added MsgBoxes: MsgBox "Copied all data from source workbook" Else MsgBox "No file selected" End If Application.Goto MasterWB.Worksheets("Master-Incoming").Range("A1"), True End Sub END CODE On Thursday, December 13, 2007 11:51 AM KenHudso wrote: I have a worksheet in which I have deleted rows at the end of the sheet in some VBA code. In VBA I now want to find the last last row that contains data. Some columns have data and some are blank so I don't know which column will consistently have data. What is the correct code I need to use to return the last row number that contians data? TIA. -- Ken Hudson On Thursday, December 13, 2007 12:14 PM James_Thomlinso wrote: I use this function a lot in my projects. It returns the last cell. You can easily get the row from that something like this... sub test msgbox lascell(sheets("Sheet1")).row end sub Public Function LastCell(Optional ByVal wks As Worksheet) As Range Dim lngLastRow As Long Dim intLastColumn As Integer If wks Is Nothing Then Set wks = ActiveSheet On Error Resume Next lngLastRow = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row intLastColumn = wks.Cells.Find(What:="*", _ After:=wks.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column On Error GoTo 0 If lngLastRow = 0 Then lngLastRow = 1 intLastColumn = 1 End If Set LastCell = wks.Cells(lngLastRow, intLastColumn) End Function -- HTH... Jim Thomlinson "Ken Hudson" wrote: On Thursday, December 13, 2007 12:22 PM Gary Keramidas wrote: jim you have a typo in your sub msgbox lasTcell(sheets("Sheet1")).row -- Gary On Thursday, December 13, 2007 12:45 PM Ron de Bruin wrote: See also http://www.rondebruin.nl/last.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm On Thursday, December 13, 2007 12:47 PM KenHudso wrote: Just what I wanted. Thanks. -- Ken Hudson "Carim" wrote: On Friday, December 14, 2007 3:33 AM Carim wrote: Hi, Have a try with : Sub GetRealLastRow() Dim RealLastRow As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).row Cells(RealLastRow, 1).Select End Sub HTH |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
Try...
Sub PopulateLineItemWorkbook_BrowserMethod() Dim wkbMaster As Workbook, wkbSource As Workbook Dim rngSource As Range, rngTarget As Range Dim wks As Worksheet Dim vFilename As Variant, vWksNames As Variant Dim i As Long, lLastRow As Long Const sSourceWksNames As String = _ "4050CC30001,301AA1234,50BB9999,65961LL3201" Set wkbMaster = Workbooks("Line items-Combined16.xlsm") ''''''''''Clear wkbMaster'''''''''''''''''''''''''''''''''''''' Application.Goto wkbMaster.Sheets("Master-Incoming").Rows("3:3") Range("E3").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).ClearContents Range("A1").Activate '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''' vFilename = Application.GetOpenFilename(, , "Please select source workbook:") If vFilename < False Then Set wkbSource = _ Workbooks.Open(Filename:=varFileName, UpdateLinks:=0) vWksNames = Split(sSourceWksNames, ",") For Each wks In wkbSource.Worksheets For i = LBound(vWksNames) To UBound(vWksNames) If wks.Name Like vWksNames(i) _ And wks.Visible < xlSheetHidden Then 'Expand Column groups, Collapse Row groups ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2 lLastRow = wks.Cells(Rows.Count, "B").End(xlUp).Row Set rngSource = wks.Range(Cells(1, 2), Cells(lLastRow, 13)) Set rngTarget = wkbMaster.Sheets("Master-Incoming").Range("A" & Rows.Count).End(xlUp).Offset(1) rngTarget.Resize(rngSource.Rows.Count, _ rngSource.Columns.Count).Value = rngSource.Value End If Next 'i Next 'wks wkbSource.Close False MsgBox "Copied all data from source workbook" Else MsgBox "No file selected" End If Application.Goto wkbMaster.Worksheets("Master-Incoming").Range("A1"), True End Sub Watch for wordwrap! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find Last Used Row
You should change your testing sub to a function.
A function can return a value (LastRow)... '--- Function testing123() As Long Dim LastRow As Long With Worksheets("4050CC30001") LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row End With testing123 = LastRow End Function '--- To use it, assign the functions return value to a variable... x = testing123() Cells(x, 47).Value = "Sludge" -OR- Use it directly in your code... Cells(testing123(), 47).Value = "Sludge" '--- You may want to take a look at my universal last row function. Download the workbook/code from... http://blog.contextures.com/archives...ith-excel-vba/ '--- Jim Cone Portland, Oregon USA . http://www.mediafire.com/PrimitiveSoftware . (free and commercial excel programs) "Rowland Hamilton" wrote in message ... Folks: I finally got my find last row formula to work but I can't seem to get it to work inside my macro with other variables. Note: My previous copy data was contiguous 1 region but now I need to copy non-contiguous copy data with the parameters in this code (B1:M & lastrow) Also, I want to use an array for worksheets so that its easier to add and remove cost centers. Finally, if I can get this licked, I'll need a separate macro with a formula for first row, since I have another set of data that resides below a table I don't need. Thank you - Rowland This worked: START CODE: Sub testing123() Dim LastRow As Long With Worksheets("4050CC30001") LastRow = Worksheets("4050CC30001").Cells(Rows.Count, "B").End(xlUp).Row .Range(Cells(1, 2), Cells(LastRow, 13)).Select End With End Sub END CODE Now how do I get that to work within this: START CODE: 'Completed Code ? Sample CC Option Explicit Sub Populate_line_item_Workbook_Browser_Method() Dim MasterWB As Workbook Dim SourceWB As Workbook Dim rngSrc As Range Dim rngDst As Range Dim ws As Worksheet Dim varFileName As Variant Dim I As Long Dim myArr As Variant Dim LastRow As Long Set MasterWB = Workbooks("Line items-Combined16.xlsm") ''''''''''Clear MasterWB'''''''''''''''''''''''''''''''''''''''''' ''''''''''' MasterWB.Sheets("Master-Incoming").Activate Rows("3:3").Select Range("E3").Activate Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.ClearContents Range("a1").Activate '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''' ?"4050CC30001", "301AA1234", "50BB9999" and "65961LL3201" myArr = Array("4050CC30001", "301AA1234", "50BB9999", "65961LL3201") 'For I = LBound(myArr) To UBound(myArr) ? Don?t know how to make array work varFileName = Application.GetOpenFilename(, , "Please select source workbook:") If TypeName(varFileName) = "String" Then Set SourceWB = Workbooks.Open(Filename:=varFileName, UpdateLinks:=0) For Each ws In SourceWB.Worksheets If ws.Name Like "4050CC30001" And ws.Visible < xlSheetHidden Or _ ws.Name Like "301AA1234" And ws.Visible < xlSheetHidden Or _ ws.Name Like "50BB9999" And ws.Visible < xlSheetHidden Or _ ws.Name Like "65961LL3201" And ws.Visible < xlSheetHidden Then 'Expand Column groups, Collapse Row groups ? need to hide lower table ActiveSheet.Outline.ShowLevels RowLevels:=1, ColumnLevels:=2 'copy ?This works for contiguous data region, but my new data is not contiguous: ?Set rngSrc = ws.Range("A3").CurrentRegion.Offset(1, 0) LastRow = ws.Cells(Rows.Count, "B").End(xlUp).Row ?I can?t get this to work: Set rngSrc = ws.Range(Cells(1, 2), Cells(LastRow, 13)) 'paste Set rngDst = MasterWB.Sheets("Master-Incoming").Range("A" & Rows.Count).End(xlUp).Offset(1) rngSrc.Copy rngDst.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False End If Next ws SourceWB.Close False ?On contiguous data, got error message when it ran out of new sheets so I _ added MsgBoxes: MsgBox "Copied all data from source workbook" Else MsgBox "No file selected" End If Application.Goto MasterWB.Worksheets("Master-Incoming").Range("A1"), True End Sub END CODE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - delete the remainder of the text in the cell after my Find | Excel Programming | |||
Find First Non blank cell than find column header and return that value | Excel Worksheet Functions | |||
Despite data existing in Excel 2002 spreadsheet Find doesn't find | Excel Discussion (Misc queries) | |||
find and delete text, find a 10-digit number and put it in a textbox | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming |