Function x1LastRow as a Range parameter

[fiqulupo]

Hey all,

I'm having difficulty with some basic syntax. I have the code below to find the last row, and then just below that, the code that I am using to try and select everything from A2:U2 down to the lastrow. Help? any ideas?

Function xlLastRow(Optional WorksheetName As String) As Long
 
     '    find the last populated row in a worksheet
 
    If WorksheetName = vbNullString Then
        WorksheetName = ActiveSheet.Name
    End If
    With Worksheets(WorksheetName)
        xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
    End With
 
End Function
 
Sub NEW_WORKSHEET()
'
'This is the sheet witht the pull of information from a database that can 
' have a varying number of rows
    Sheets("Sheet1").Select
    Range("A1", "U1").Select
    ActiveSheet.Range(Selection, x1lastrow).Select
    Selection.Copy
    Sheets.Add.Name = "Master Source"
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

 

[Firefly2012]

Because identifying the last row can be written all in one line, I personally don't think there is much point writing a separate function - what you have could be rewritten as:

Dim rLastRow As Range

Set rLastRow = Cells.Find(What:="*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious,LookIn:=xlFormulas)

'check value found:
If Not rLastRow Is Nothing Then
   Range("A1:U" & rLastRow.Row).Copy
   Sheets.Add.Name = "Master Source"
   Range("A1").PasteSpecial Paste:=xlPasteValues
Else
  MsgBox "No values found!"
End If

 

[fiqulupo]

Hey Firefly2012, thanks so much for your recommendation. That really cut down the code quite a bit and made the macro run so much more quickly!

Thank you!

 

[Jonmo1]

Glad you have resolution, and I agree with Firefly that it's a very simple task, no need for a seperate function...

But I've identified a couple issues that should shed some light on why your code didn't work...

1. The name of your function is xlLastRow
Function xlLastRow(Optional WorksheetName As String) As Long

But you've misspelled it when you tried to use it
ActiveSheet.Range(Selection, x1lastrow).Select
You spelled it here with the NUMBER 1, but the name of the function uses the LETTER L


2. Your function only returns a number, the last used row# on the sheet.
So suppose that # is 50 for example.
so xlLastRow = 50

Then this
Range("A1", "U1").Select
ActiveSheet.Range(Selection, x1lastrow).Select

becomes
Range("A1", "U1").Select
ActiveSheet.Range(Selection, 50).Select

That doesn't make sense...

What you're trying to do is to select from Row 1 to 50 of the selected range..right?

That should be written like

Range("A1:U" & xlLastRow).Select


Hope that helps..