CODEWITHSUNDEEP
excelvba
Prayag
Prayag

Reputation: 211

Delete Rows and Columns until specific range

I have an Excel workbook in which I search for a date in the heading using .Find.

For example if it is found in RANGE("C3") then it should remove rows above and columns before it.

Please help me write the VBA code to perform this operation.

Dim WSD As Worksheet
Set WSD = Worksheets("report")
Dim MyRange As Range
Set MyRange = WSD.Range("A:AZ")
Set R = MyRange.Find("Date", LookIn:=xlValues)
Debug.Print R.Address

Upvotes: 0

Views: 156

Answers (2)

DisplayName
DisplayName

Reputation: 13386

another way:

Dim MyRange  As Range
With Worksheets("report")
    Set MyRange = .Range("A:AZ").Find("Date", LookIn:=xlValues)
    If Not MyRange Is Nothing Then MyRange.Resize(.UsedRange.Rows.Count + MyRange.Row, .UsedRange.Columns.Count + MyRange.Column).Cut Destination:=.Range("A1")
End With

Or

Dim MyRange  As Range
With Worksheets("report")
    Set MyRange = .Range("A:AZ").Find("Date", LookIn:=xlValues)
    If Not MyRange Is Nothing Then
        .Rows(1).Insert
        .Columns(1).Insert
        With .Range("A1", MyRange)
            .Resize(, .Columns.Count - 1).EntireColumn.Delete
            .Resize(.Rows.Count - 1).EntireRow.Delete
        End With
    End If
End With

Upvotes: 1

user4039065
user4039065

Reputation:

Try,

dim MyRange  as range

with Worksheets("report")
    Set MyRange = .Range("A:AZ").Find("Date", LookIn:=xlValues)
    if not MyRange is nothing then
        if MyRange.column > 1 then
            .cells(1, 1).resize(1, MyRange.column -1).entirecolumn.delete
        end if
        if MyRange.row> 1 then
            .cells(1, 1).resize(MyRange.row-1, 1 ).entirerow.delete
        end if
    end if
end with

Upvotes: 1

Related Questions