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

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

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

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

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

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

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

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

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