Delete Rows

[DougW]

I would like to write code that would find text in column A and then select
all rows to the last row, and finally delete all the selected rows including
the original row where the text was found.

I am able to find the text, no problem, and the text is unique to the first
column. I used ActiveCell.End(xlDown) to get to the last row. But, it doesn't
select all the rows.

I tried finding the text and then using:

strBeginCell = Selection.Addres

as the beginning address, then using the following to get the ending cell
address:

ActiveCell.End(xlDown).Select
strEndCell = Selection.Address

And, finally using the following to select all the rows, at which point I
get a syntax error:

Range(strBegCell:strEndCell).Select

Thank you.

 

[merjet]

Range(strBegCell & ":" & strEndCell).Select

Hth,
Merjet

 

[JLGWhiz]

Sub delColA()
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
If Range("A1") = "" And Range("A2") = "" Then
x = Range("A1').End(xlDown).Row
Else
x = Range("A2").Row
End If
Range("A" & x & ":A" & lastRow).Delete
End Sub

 

[JLGWhiz]

Actually, if you don't have any data in column A that you want to save, you
could just use:

Columns("A").ClearContents

 

[Mike H]

Maybe

Sub standard()
Dim myrange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myrange = Sheets("Sheet1").Range("A1:A" & LastRow)
For Each c In myrange
If UCase(c.Value) = "TEST" Then 'Change you your string
LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Range("A" & c.Row & ":A" & LastRow).EntireRow.ClearContents
End If
Next
End Sub

Mike

 

[Rick Rothstein \(MVP - VB\)]

You can use this subroutine to find the word in Column A and then delete
that row on down...

Sub FindAndDeleteDown(WordToFind As String)
Dim LastRow As Long
Dim StartRow As Long
With Worksheets("Sheet5")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For StartRow = 1 To LastRow + 1
If StrComp(.Cells(StartRow, "A").Value, _
WordToFind, vbTextCompare) = 0 Then Exit For
Next
If StartRow < LastRow + 1 Then .Rows(StartRow & ":" & LastRow).Delete
End With
End Sub

Just call this subroutine from within your own passing the word you want to
find as its argument. For example...

SubTest()
FindAndDeleteDown "My Word"
End Sub

Rick

 

[Rick]

A simple answer would be to resize using;


ActiveCell.Resize(x, y).Select

With x being the number of rows and y being the number of columns.

Followed by:
Selection.ClearContents

Depending on whether you want to just clear,

or
Selection.Delete Shift:=xlUp

if you want to delete.