VBA Excel select content after the character occurrence

user_6694814 0
Viewed 36

I want to select the content of my worksheet after the character occurrence in the row.

The code I have been using so far selects all the stuff after the offset.

 Sub Selection ()
 Dim Target_Start As Range, Target_End As Range

 Dim n as long

 Set Target_Start = work.Cells.Find("X", SearchOrder:=xlByColumns).Offset(1)
 Set Target_End = Target_Start.End(xlDown).End(xlToRight)


 work.Range(Target_Start, Target_End).Select
 'Selection.EntireRow.Clear


 End Sub

enter image description here

What should I alter in my code?

vba excel
1 Answers

Select Used Range After a Cell

  • If you want to allow a lower-case "X" then replace True with False (MatchCase).
  • Note that this solution will include row 15 in your image which may be only formatted (borders, no values), because we are using Used Range. If you don't like that, you will have to use another way to define the range.
Option Explicit

Sub selectAfter()
    Const sString  As String = "X"
    With work.UsedRange
        Dim rg As Range
        Set rg = .Find(sString, .Cells(.Rows.Count, .Columns.Count), _
            xlFormulas, xlWhole, xlByRows, , True)
        If Not rg Is Nothing Then
            Dim Offs As Long: Offs = rg.Row - .Row + 1
            Set rg = .Resize(.Rows.Count - Offs).Offset(Offs)
            rg.Select
        End If
    End With
End Sub
user_98140690
Related