Find Last Used Row

[Ken Hudson]

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.

 

[Carim]

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

 

[Jim Thomlinson]

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

 

[Gary Keramidas]

jim
you have a typo in your sub

msgbox lasTcell(sheets("Sheet1")).row

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/last.htm

 

[Ken Hudson]

Just what I wanted.
Thanks.

 

[GS]

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!

 

[Jim Cone]

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/2011/07/18/find-last-row-with-excel-vba/

'---
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)






"Rowland Hamilton" <[email protected]>
wrote in message