Clash Royale CLAN TAG#URR8PPP

Delete Rows and Columns until specific range

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

.Find

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

RANGE("C3")

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


2 Answers
2

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


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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.