issue with using range that I have obtained

[Oberon70]

Hi,

I keep receiving the below message:

for the below macro

Sub Delete_Last_2_Rows()

Dim i As Long
Dim rng_1 As Range
Dim rng_2 As Range
Dim ws As Worksheet
Set ws = Sheets("Data")
Set rng_1 = Range("A:A")

ws.Select

i = WorksheetFunction.CountA(rng_1) + 2

'MsgBox "A" & i

'MsgBox FindLast(3)

'MsgBox "A" & i & ":" & FindLast(3)

Set rng_2 = ws.Range("A" & i & ":" & FindLast(3))

ws.Rows(rng_2).Select
    Selection.Delete Shift:=xlUp

End Sub

FindLast runs a macro that obtains the last cell for the last row with data in it.

The below is A & the variable i combined

The below is the results from FindLast(3)

when I run MsgBox rng_2.address

I appreciate any help.

 

[Joe4]

IF you hit the "Debug" button, which line of code does it highlight?

What exactly is "FindLast"?
Is that a User Defined Function you have somewhere else?
If so, please post the code for that for us to see.

 

[Oberon70]

Yes, FindLast is a Function I obtained through a YouTube course and the below line of code is what is highlighted

Set rng_2 = ws.Range("A" & i & ":" & FindLast(3))

and below the Function

Function FindLast(lRowColCell As Long, _
                    Optional sSheet As String, _
                    Optional sRange As String)
'Find the last row, column, or cell using the Range.Find method
'lRowColCell: 1=Row, 2=Col, 3=Cell

Dim lRow As Long
Dim lCol As Long
Dim wsFind As Worksheet
Dim rFind As Range

    'Default to ActiveSheet if none specified
    On Error GoTo ErrExit
   
    If sSheet = "" Then
        Set wsFind = ActiveSheet
    Else
        Set wsFind = Worksheets(sSheet)
    End If

    'Default to all cells if range no specified
    If sRange = "" Then
        Set rFind = wsFind.Cells
    Else
        Set rFind = wsFind.Range(sRange)
    End If
   
    On Error GoTo 0

    Select Case lRowColCell
   
        Case 1 'Find last row
            On Error Resume Next
            FindLast = rFind.Find(What:="*", _
                            After:=rFind.Cells(1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Row
            On Error GoTo 0

        Case 2 'Find last column
            On Error Resume Next
            FindLast = rFind.Find(What:="*", _
                            After:=rFind.Cells(1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
            On Error GoTo 0

        Case 3 'Find last cell by finding last row & col
            On Error Resume Next
            lRow = rFind.Find(What:="*", _
                           After:=rFind.Cells(1), _
                           LookAt:=xlPart, _
                           LookIn:=xlFormulas, _
                           SearchOrder:=xlByRows, _
                           SearchDirection:=xlPrevious, _
                           MatchCase:=False).Row
            On Error GoTo 0

            On Error Resume Next
            lCol = rFind.Find(What:="*", _
                            After:=rFind.Cells(1), _
                            LookAt:=xlPart, _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious, _
                            MatchCase:=False).Column
            On Error GoTo 0

            On Error Resume Next
            FindLast = wsFind.Cells(lRow, lCol).Address(False, False)
            'If lRow or lCol = 0 then entire sheet is blank, return "A1"
            If Err.Number > 0 Then
                FindLast = rFind.Cells(1).Address(False, False)
                Err.Clear
            End If
            On Error GoTo 0

    End Select
   
    Exit Function
   
ErrExit:

    MsgBox "Error setting the worksheet or range."

End Function

 

[Joe4]

It looks like from your code that "FindLast" returns a number.

If that is the case, then this line will cause an error:

Set rng_2 = ws.Range("A" & i & ":" & FindLast(3))

as you are missing the column letter after the ":" and before the "FindLast(3)".

So if i were to return 5 and "FindLast(3)" were to return a number like 17, your range would look like this;

ws.Range("A5:17")

which is not a valid range reference.
You need a column letter between the ":" and "17".

 

[Oberon70]

I ran Findlast Function by it self in a message box

and it appears it is giving me the the column letter and number.

 

[Joe4]

Can you share a small sample of test data that we can try to run your code against (data that will return the error)?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RoryA]

What do you expect ws.Rows(rng_2).Select to do, given that rng_2 is a multi-cell range (ie an array of values)?

 

[Oberon70]

I think I have found another solution. I have another Macro that would delete the row if the 1st cell was blank. I will run this macro prior to the range being found and a Define Name being created.

I still want to eventually understand this better, but I am rushing as vba will automated a couple of task that I manually do.

I usually sort around 20 statements each month manually, which includes consolidate the transactions on the statements. It reduc3s the amount of entries we have to enter into our system.

I will still upload the code as I would like to understand what the issue is. I have also paid for an online course I will start next week.

 

[Joe4]

OK, if you still want help, we are willing to help you, but please address the last posts from myself and Rory.
I would like to try to "recreate" your scenario, and follow the same steps as you, and see exactly what is happening. So I want to work on the same data structure you are, so am requesting a data sample.
And Rory is questioning your expected behavior.

If you can address those questions/issues, we may be able to help you sort through this.

 

[Oberon70]

OK, if you still want help, we are willing to help you, but please address the last posts from myself and Rory.
I would like to try to "recreate" your scenario, and follow the same steps as you, and see exactly what is happening. So I want to work on the same data structure you are, so am requesting a data sample.
And Rory is questioning your expected behavior.

If you can address those questions/issues, we may be able to help you sort through this.

Yep, I am still planing to do that. It likely won't be until tomorrow night as it is nearly midnight here and tomorrow is a public holiday and I promised my wife I would spend it out with the family.