Last Row in range but ignore numbers

[KDS14589]

I have the following code for finding the last row in a range

 Dim LDR1 As Long: LDR1 = .Range(.Cells(5, "C"), .Cells(150, LDC1)).Find("*", , , , xlByRows, xlPrevious).Row

How can I modify this to ignore numbers or non-text items, or do I need a new formula?

 

[rlv01]

I'm not sure I understand your need, but If you are just looking for the last row of a range, there are many different ways. Here's one.

       Dim LDR1 As Long, SA As Variant
       SA = Split(.Range(.Cells(5, "C"), .Cells(150, LDC1)).Address, "$")
       LDR1 = CLng(SA(UBound(SA)))

 

[Artik]

Consider using the LastRowWithText function below:

Sub Test()
    Dim lRow As Long

    lRow = LastRowWithText(Columns("A"))

    MsgBox lRow
End Sub


Function LastRowWithText(rngInput)
    Dim rng As Range
    Dim rngF As Range
    Dim rngConst As Range
    Dim rngFrml As Range
    Dim blnIsRngConst As Boolean
    Dim blnIsRngFrml As Boolean
    Dim i As Long
    Dim lLastRow As Long

    On Error Resume Next

    '1 means numbers, 2 - texts, 4 - logical values, 16 - error values. ------------\
    'Bit combinations can also be used, e.g., 7 is numbers, text and logical values.|
    '                                                                               |
    'find cells with text constant values                                           |
    Set rngConst = rngInput.SpecialCells(xlCellTypeConstants, 2)  '<----------------/

    'find cells with text values of formulas
    Set rngFrml = rngInput.SpecialCells(xlCellTypeFormulas, 2)

    blnIsRngConst = Not rngConst Is Nothing
    blnIsRngFrml = Not rngFrml Is Nothing

    If Not (blnIsRngConst Or blnIsRngFrml) Then
        Set rng = Nothing
    ElseIf Not blnIsRngConst And blnIsRngFrml Then
        Set rng = rngFrml
    ElseIf blnIsRngConst And Not blnIsRngFrml Then
        Set rng = rngConst
    ElseIf blnIsRngConst And blnIsRngFrml Then
        Set rng = Union(rngConst, rngFrml)
    End If

    For Each rngF In rng.Areas
        With rngF
            If lLastRow < .Row + .Rows.Count - 1 Then
                lLastRow = .Row + .Rows.Count - 1
            End If
        End With
    Next rngF
    On Error GoTo 0

    If lLastRow = 0 Then lLastRow = 1

    LastRowWithText = lLastRow

End Function

Artik

 

[Akuini]

How can I modify this to ignore numbers or non-text items,

Could you provide an example of your data?

 

[Akuini]

If I understand you correctly, any cell in your dataset is either number or text, something like this:

Book1
	A	B	C
1	X	P	U
2		3	L
3	3	W
4	3	S	I
5	3	3	3
6	1		1
7
Sheet4

and you want to get the last row with text in it, in this example it's row 4 instead of 6.
Here's another code to try:

Sub KDS14589_1()
Dim c As Range
Dim last_row As Long
Dim sAddress As String
With ActiveSheet.Range("A:C")
    Set c = .Find(What:="*", LookIn:=xlValues, After:=Range("A1"), LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            sAddress = c.Address
            Do
               If VarType(c) = vbString Then
                    last_row = c.Row
                    Exit Do
               End If
               Set c = .Find(What:="*", LookIn:=xlValues, After:=c, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
            Loop While Not c Is Nothing And c.Address <> sAddress
        End If
End With
Debug.Print last_row 'returns 4
End Sub

 

[KDS14589]

If I understand you correctly, any cell in your dataset is either number or text, something like this:

Book1
	A	B	C
1	X	P	U
2		3	L
3	3	W
4	3	S	I
5	3	3	3
6	1		1
7
Sheet4

and you want to get the last row with text in it, in this example it's row 4 instead of 6.
Here's another code to try:

Sub KDS14589_1()
Dim c As Range
Dim last_row As Long
Dim sAddress As String
With ActiveSheet.Range("A:C")
    Set c = .Find(What:="*", LookIn:=xlValues, After:=Range("A1"), LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        If Not c Is Nothing Then
            sAddress = c.Address
            Do
               If VarType(c) = vbString Then
                    last_row = c.Row
                    Exit Do
               End If
               Set c = .Find(What:="*", LookIn:=xlValues, After:=c, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
            Loop While Not c Is Nothing And c.Address <> sAddress
        End If
End With
Debug.Print last_row 'returns 4
End Sub

Akuin: I tried your code just once and had problems but if I worked longer, it may work. I went back to my original code but i added a work-around that is working.

  Dim cellClear As Range
        For Each cellClear In .Range(.Cells(5, "C"), .Cells(150, LDC1))
            If (IsEmpty(cellClear) And IsEmpty(cellClear.Offset(1, 0))) Then
                cellClear.Offset(2, 0).Clear
            End If
            If IsNumeric(cellClear) Then
                cellClear.Clear
            End If
            Next cellClear
            
    Dim LDR1 As Long: LDR1 = .Range(.Cells(5, "C"), .Cells(150, LDC1)).Find("*", , , , xlByRows, xlPrevious).Row      '''LastDataRow in chart
             MsgBox "Last header in 4th row is column # " & LDC1 & " (" & Split(Cells(, LDC1).Address, "$")(1) & ") with a data collection for """ & LVC1 & """ and Last Data Row is " & LDR1

Many thanks anyway
I'll keep on working on it my spare time

 

[Rick Rothstein]

You did not post your whole code, so I am not sure what With statement your dots the Range and Cell objects are referencing, but if I am not mistaken, this non-looping code snippet should be able to replace the code snippet you posted...

  With .Range(.Cells(1, "A"), .Cells(6, "C"))
    MsgBox Evaluate(Replace("MAX(IF(ISERROR(@+0),ROW(@),""""))", "@", .Address))
  End With