VBA LastRow to Specific Text

[decadence]

Hi, Is it possible to set a range in a column so it's set from last row to specific text found (header) but doesn't include the header.

Example 1 - Sets the range so only the 2 rows found (Nothing,Something)
[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Specific Text[/TD]
[/TR]
[TR]
[TD]Nothing[/TD]
[/TR]
[TR]
[TD]Something[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Example 2 - Sets the range so only the 6 rows found (Something,Something,Something,Something,Nothing,Nothing)

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Specific Text[/TD]
[/TR]
[TR]
[TD]Something[/TD]
[/TR]
[TR]
[TD]Something[/TD]
[/TR]
[TR]
[TD]Something[/TD]
[/TR]
[TR]
[TD]Something[/TD]
[/TR]
[TR]
[TD]Nothing[/TD]
[/TR]
[TR]
[TD]Nothing[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[njimack]

Assuming your data starts from column A...

Sub x()
Dim LastRow As Long
Dim rng1 As Range
With Sheets("Sheet1")
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    
    rng1 = Range(.Cells(2, 1), .Cells(LastRow, 1))
End With
    
End Sub

 

[decadence]

Hi njimack, My data could vary in which column the data is. Also the Rows are not static

 

[njimack]

Is it a single column of data? Can you post a small sample of your data?

 

[decadence]

Yes it will be a single column of data to set a range for but the data could start anywhere i.e. D4 or T100, unfortunately I can't give you a sample due to confidentiality of information.

 

[njimack]

See if this works for you...

Sub x()
Dim LastRow As Long, Column As Long, FirstRow As Long
Dim rng1 As Range
With Sheets("Sheet1")
    LastRow = .Cells.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Column = Rng.Find(What:="*", After:=Rng.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    FirstRow = .Cells(LastRow, LastColumn).End(xlUp).Row [COLOR=#FF0000]+1[/COLOR]
    
    rng1 = Range(.Cells(FirstRow, Column), .Cells(LastRow, Column))
End With
    
End Sub

 

[njimack]

Oops - ignore the last past, quite a few errors in the code. This should work...

Sub x()
Dim LastRow As Long, Column As Long, FirstRow As Long
Dim rng1 As Range
With Sheets("Sheet1")
    LastRow = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    Column = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
    FirstRow = .Cells(LastRow, Column).End(xlUp).Row +1
    
    Set rng1 = Range(.Cells(FirstRow, Column), .Cells(LastRow, Column))
    MsgBox rng1.Address
End With
    
End Sub

 

[Fluff]

Another option might be

Sub GetRng()

    Dim Ans As String
    Dim Fnd As Range
    Dim Rng As Range
    
    Ans = InputBox("Please enter header name")
    If Len(Ans) = 0 Then Exit Sub
    Set Fnd = Cells.Find(Ans, Range("A1"), xlValues, xlWhole, xlByRows, xlNext, False, False)
    If Not Fnd Is Nothing Then
        Set Rng = Range(Fnd.Offset(1), Cells(Rows.Count, Fnd.Column).End(xlUp))
    End If
    Rng.Select
    
End Sub

 

[decadence]

Hi Fluff, This is what I am looking for but I don't want the users to be able to enter anything. Also the specific word and the data below it are in different cell locations so i can't use [A1] as the range.

 

[Fluff]

A1 is simply the start point for the find.
What is the specific word that you want to find?